What If Parameter to change # of periods in MoM bar chart

[thrillhouse]

I have a bar chart showing MoM % change in sales.  I'd like to use a What-if parameter to change the number of months in the view.  I'd like to set it for a rolling 12 months, but provide the end user the ability to look further back in history if desired. This was easy enough to do with a line chart just showing monthly sales.  I used the following DAX expression to acheive this:

Sales (last n months) =

CALCULATE(SUM('Table'[Sales]), DATESINPERIOD('Date'[Date].[Date], MAX('Date'[Date].[Date]), -'N Value'[N Value Value], MONTH))

I am having trouble using a similar logic for the MoM expression.  I am currently just using the canned quick-measure DAX and trying to work in similar logic as shown above:

Sales MoM % Change =

IF(

    ISFILTERED('Date'[Date]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __PREV_MONTH =

        CALCULATE(

            SUM('Table'[Sales]),

            DATEADD('Date'[Date].[Date], -1, MONTH)

        )

    RETURN

        DIVIDE(

            SUM('Table'[Sales])

                - __PREV_MONTH,

            __PREV_MONTH

        )

)

Any help is greatly appreciated!

[amitchandak]

@thrillhouse , you want to compare a month with N month back. or rolling N with rolling N

Assum N = 'N Value'[N Value Value]

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last Nth  MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1*N,MONTH)))
Nth last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth(dateadd('Date'[Date],-1*N,MONTH))-1)

N vs N

Rolling N = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],eomonth(MAX(Sales[Sales Date]),-1),-1*N ,MONTH))

Rolling n till last n month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],maxX(Sales, dateadd(Sales[Sales Date],-1 *N,month)),-1*N,MONTH))

[thrillhouse]

Hi @amitchandak, 

Thanks for your reply!  I'm not looking to compare N month to N month or rolling N with rolling N.  I'm just looking to get change from previous month for the last N months.  I currently am just displaying 12 months, but I have more than 12 months of historical data and would like to give the end user the ability to add more months to the axis if desired.  I have done this in a line chart showing monthly sales sums using the following expression: 

Sales (last n months) =

CALCULATE(SUM('Table'[Sales]), DATESINPERIOD('Date'[Date].[Date], MAX('Date'[Date].[Date]), -'N Value'[N Value Value], MONTH))

This just allows me to enter a number into the what-if parameter and see that many number of periods in the line chart.   I am looking to have the same functionality in this bar chart that shows change from previous month for the last N months.

[amitchandak]

@thrillhouse , I think this video should help you

https://www.youtube.com/watch?v=duMSovyosXE

Same in my attached file in the last 12. You can replace that number with of what if 

[thrillhouse]

I'm having serious trouble making any of these solutions work with a Month to Month % Change calculation.  They work no problem with normal measures, but for some reason I just cannot seem to figure out how to fit the Month to Month % change calculation into this.  

[v-yiruan-msft · Accepted solution]

Hi @thrillhouse ,

Could you please provide your sample pbix file(exclude sensitive data) if it is convenient? It is a little difficult to find the cause of problem without the data and relationship info... Thank you.

In addition, please check the content in the following links. Hope they can help you.

Calculating Month On Month Change - Custom (445) Calendars, Power BI & DAX

Quick Measure MoM%

Best Regards