selected month v previous month DAX HELP please

[Anonymous]

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!

[amitchandak · Accepted solution]

@Anonymous , 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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy

Appreciate your Kudos.

[Anonymous]

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

[Anonymous]

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

[v-shex-msft]

Hi @Anonymous,

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

[Anonymous]

@v-shex-msft 

@Thank you for sharing knowledge.

[nandukrishnavs]

@Anonymous 

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 🙂