cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.