Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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 , 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.
@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.
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.
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
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 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |