Search code examples
ssaspowerbidaxpowerpivot

Dynamic X-Axis Date Range Based on Disconnected Slicer

I have an issue that I need a hand with. I am using a disconnected slicer based off of this table 

Time Frame := 
DATATABLE("ID", Integer, "Time Frame", string
, {
  {1, "3 Month"}
, {2, "6 Month"}
, {3, "9 Month"}
, {4, "12 Month"}
})

that looks like this:

enter image description here

Using a few DAX measures:

Sum Of Paid := sum(Data[Paid])

Sum Of Paid 3 Month Period :=
CALCULATE (
    [Sum Of Paid],
    DATESINPERIOD ( Data[Date], LASTDATE ( Data[Date] ), -3, MONTH )
)

Sum Of Paid 6 Month Period :=
CALCULATE (
    [Sum Of Paid],
    DATESINPERIOD ( Data[Date], LASTDATE ( Data[Date] ), -6, MONTH )
)

Sum Of Paid 9 Month Period :=
CALCULATE (
    [Sum Of Paid],
    DATESINPERIOD ( Data[Date], LASTDATE ( Data[Date] ), -9, MONTH )
)

Sum Of Paid 12 Month Period :=
CALCULATE (
    [Sum Of Paid],
    DATESINPERIOD ( Data[Date], LASTDATE ( Data[Date] ), -12, MONTH )
)

Slicer - Paid Per Period :=
VAR selection =
    IF (
        HASONEVALUE ( 'Time Frame'[Time Frame] ),
        VALUES ( 'Time Frame'[Time Frame] ),
        "All"
    )
VAR ThreeMonth = [Sum of Paid 3 Month Period]
VAR SixMonth = [Sum of Paid 6 Month Period]
VAR NineMonth = [Sum of Paid 9 Month Period]
VAR TwelveMonth = [Sum of Paid 12 Month Period]
RETURN
    SWITCH (
        TRUE (),
        selection = "3 Month", ThreeMonth,
        selection = "6 Month", SixMonth,
        selection = "9 Month", NineMonth,
        selection = "12 Month", TwelveMonth,
        TwelveMonth
    )

I can simulate a slicer and display 3, 6, 9 and 12 month aggregates depending on the users selection. These work just fine in cards, choropleths and almost everything I've needed them for. My issue is trying to create line graphs, bar charts, etc that have a date range on the x-axis. I can't for the life of me figure out how to replicate the period referenced in the DAX measure to dynamically set the x-axis.

I've tried a few approaches that seem like they should work but do not:

3 Month Period :=
CALCULATE (
    VALUES(Data[Date]),
    DATESINPERIOD ( Data[Date], LASTDATE ( Data[Date] ), -3, MONTH )
)

Any help is appreciated.

Thanks

Solution

  • You might want to try this one:

    3 Month Period :=
CALCULATE (
    [Sum Of Paid],
    KEEPFILTERS (
        DATESINPERIOD (
            Data[Date],
            CALCULATE (
                MAX ( Data[Date] ),
                ALLSELECTED ()
            ),
            -3,
            MONTH
        )
    )
)