cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

selected month v previous month DAX HELP please

Hello PBI users,

I am new to Power BI and am currently exploring the tool.

Need guidance on DAX to create/show Selected month vs Previous month compared side by side (on cards).

Month selection should be dynamic, meaning, will be offered to end user for their selection. 

 

In Tableau, we create a parameter of date selected say "selecteddate"and create calculated field like

Selected month: if month(orderdate) = month(selecteddate) then sales

Previous month: if month(orderdate) = date_add('month',-1, selecteddate) then sales

Need to replicate this on PBI using DAX.

 

Please help!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: selected month v previous month DAX HELP please

@jay5300 , in case you have date in your table table you can use time intelligence with a date table

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

 

In case you do not have then you need have month rank to work

like

Period Rank = RANKX(all('Date'),'Date'[Period Start date],,ASC,Dense)

This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])-1))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy

Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

6 REPLIES 6
Highlighted
Community Champion
Community Champion

Re: selected month v previous month DAX HELP please

@jay5300 

In Power BI, we cannot pass slicer selection value to the calculated column. Instead, you can define DAX measures. 

You can try something like this

Measure =
VAR selecteddate =
    SELECTEDVALUE ( 'Table'[orderdate] )
VAR previousmonthsales =
    CALCULATE (
        [sales],
        MONTH ( 'Table'[orderdate] )
            = MONTH ( selecteddate ) - 1
    )
RETURN
    previousmonthsales



Did I answer your question? Mark my post as a solution!
Appreciate with a kudos
🙂

Highlighted
Community Support
Community Support

Re: selected month v previous month DAX HELP please

Hi @jay5300,

In fact, measure/filter and calculate column/table are works on different data levels that you can't use calculated column to get measure/filter selections.

Measure/filter are works on virtual tables generated from data model tables, child-level filters not able to affect its parent. (notice: calculated column/table, raw table fields are hosts on data model tables)

If you want to achieve your filter to achieve the selector effect instead of fitler, it required you to use unconnected table fields as source of the slicer or they will filter your table records first that will affect your calculations.
After you created your slicer, you can use dax function to extract selected value and do some avaneed conversions and calculations in your dax formula.

Regards,

Xiaoxin Sheng

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

Re: selected month v previous month DAX HELP please

@amitchandak 

Hello Amit, Apologies for tagging directly to this post.

but, am stuck on this calculation for long time and is the reason i tagged seeking your attention.

 

Appreciate your help on this.

Highlighted
Super User IV
Super User IV

Re: selected month v previous month DAX HELP please

@jay5300 , in case you have date in your table table you can use time intelligence with a date table

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

 

In case you do not have then you need have month rank to work

like

Period Rank = RANKX(all('Date'),'Date'[Period Start date],,ASC,Dense)

This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])-1))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy

Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Helper I
Helper I

Re: selected month v previous month DAX HELP please

@amitchandak 
You are awesome.

Below solution worked perfectly.

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

 

Thank you very much.

Highlighted
Helper I
Helper I

Re: selected month v previous month DAX HELP please

@v-shex-msft 

@Thank you for sharing knowledge.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors