Forum Discussion

damit23183's avatar
damit23183
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago
Solved

Caculate Last N Month by using Slicer

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

Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      damit23183
      Icon for Microsoft Employee rankMicrosoft Employee
      5 years ago

      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

       

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    5 years ago

    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.