Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
thrillhouse
Helper I
Helper I

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

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!
1 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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@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))

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.

@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 

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.  

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.