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.
Hi,
I tried to create a card that should automatically show the sum of total in the previous month. I have created the measure that works in the table and when i put date or month in there but when there is no filter at all and i just want to show automatically every month the card with the previous month sold, it gives me blank?
I attached the file.
https://github.com/userdata21/file/blob/master/card_not%20showing_previousmonth.pbix
Many thanks!
Solved! Go to Solution.
@userdata I think you want this measure:
PrevMonth =
IF (
ISINSCOPE ( 'Date'[Month] ),
CALCULATE (
TOTALMTD ( SUM ( total[sold ] ), 'Date'[Date] ),
PREVIOUSMONTH ( 'Date'[Date] )
),
LASTNONBLANKVALUE (
'Date'[Month],
CALCULATE (
TOTALMTD ( SUM ( total[sold ] ), 'Date'[Date] ),
PREVIOUSMONTH ( 'Date'[Date] )
)
)
)
Is that what you are going for?
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
@userdata , a previous month can from time intelligence need a date. And when we use a calendar, it takes the end date to the calendar if no date is given. So stop your calendar at today or eomonth of today
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE(sum('table'[total hours value]),previousmonth('Date'[Date]))
Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
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/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
Appreciate your Kudos.
@amitchandak Thanks for that. I tried it previous month and dateadd but the problem is that i cant say use today because in my date from sold table there is not date for today but the data goes only until end of last month. When i tried using today, it gave me blank and i want it in a card where it automatically changes based on last month?
Anyone ideas how to show the last month in the card when date for sold just has data until last month. I use relative date filtering and change it to last month, to show the last month in the card but how can I do this in Dax without using the relative date filtering to filter last month?
@userdata I think you want this measure:
PrevMonth =
IF (
ISINSCOPE ( 'Date'[Month] ),
CALCULATE (
TOTALMTD ( SUM ( total[sold ] ), 'Date'[Date] ),
PREVIOUSMONTH ( 'Date'[Date] )
),
LASTNONBLANKVALUE (
'Date'[Month],
CALCULATE (
TOTALMTD ( SUM ( total[sold ] ), 'Date'[Date] ),
PREVIOUSMONTH ( 'Date'[Date] )
)
)
)
Is that what you are going for?
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
@DataZoe omg thanks sooo much! Youre a herooo! Yes that was what I was looking for. Can you maybe explain the logic how the Dax works to understand the steps? Maaaaany thaaaanks @datazoe!!
@userdata of course! I'm glad it was what you were looking for 🙂
First, the measure checks to see if it's being filtered down to a specific month with the ISINSCOPE( 'Date'[Month]).
PrevMonth =
IF (
ISINSCOPE ( 'Date'[Month] ),
CALCULATE (
TOTALMTD ( SUM ( total[sold ] ), 'Date'[Date] ),
PREVIOUSMONTH ( 'Date'[Date] )
),
LASTNONBLANKVALUE (
'Date'[Month],
CALCULATE (
TOTALMTD ( SUM ( total[sold ] ), 'Date'[Date] ),
PREVIOUSMONTH ( 'Date'[Date] )
)
)
)
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |