Caculate Last N Month by using Slicer

[damit23183]

Hi,

I am trying to calculate count by using slicer which means when i select monthyear and put number in parameter slicer than i should get value in bar chart.

For Example, Slicer is created base on Month so if put number 5 in slicer (Which is parameter created for Month) and then select Month from another slicer (e.g September 2020) then I would like to see data for last 5 months from today.

Please see screen shot below What I am trying to achieve here,

In screen shot below, when i choose "CY MONT-YEAR" = September - 2020 (Simple FIlter based on Month-Year from Date Table)

and "SINCE LAST 5 MONTHS" (This is another slicer = 5 from Parameter) then i would like to see data for last 5 months in barchart from September - 2020.

To calculate count for Last N Months, this is DAX I have used but not working;

CALCULATE(DISTINCTCOUNT(ID), DATESINPERIOD('Date'[Date],MAX('Date'[Date]), -[N Value], MONTH))

ID = Counting ID

Date[Date] = Date column from Date Table

N Value = Parameter value (1-24)

Thanks

[v-yingjl · Accepted solution]

Hi @damit23183 ,

I have created a simliar sample file in the below that you can check, hopes it could help you in some ways.

Create a new year-month column both in date table and acutal table:

YearMonth = YEAR([Date])*100+MONTH([Date])

Create a measure like this, put it in the visual filter and set its value as 1:

Measure =
VAR tab =
    TOPN (
        SELECTEDVALUE ( Parameter[Parameter] ),
        CALCULATETABLE (
            DISTINCT ( 'Table'[YearMonth] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[YearMonth] <= SELECTEDVALUE ( 'Date'[YearMonth] )
            )
        ),
        [YearMonth]
    )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[YearMonth] ) IN tab, 1, 0 )

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@damit23183 , You formula seem correct, You try this version

Rolling N =
var _max = MAXX(allselcted('Date'),'Date'[Date ])
return
CALCULATE(DISTINCTCOUNT(ID),DATESINPERIOD('Date'[Date ],_max,-1 * [N Value],MONTH))

Yours is also correct
Rolling N = CALCULATE(DISTINCTCOUNT(ID),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-1 * [N Value],MONTH))

But both will not give last 5-month trend, They will give you rolling 5 months. for trend refer this: https://www.youtube.com/watch?v=duMSovyosXE

[damit23183]

Hi Amit,

THanks for your response.

I have tried both your formula as well as formula from you tube channel.

Formula looks like working but visulization is only showing one month data every time whenever i choose or select specific month number.

Not sure, whether if there is any other setting to see for more columns in bar chart because I have been only able to see single chart so far. This is the case with all months for example if i select January 2020 then its only shows January 2020 data instead of other months data.

Please see below screen shot how does bar chart looks like,

NOTE: I have tried to change month from slicer for many times and result is only one column instead more than one column.

Thanks