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 have to compare the previous month's sales with the current month's sales.
I have two formulas:
MTD Current month =
TOTALMTD([sales],sales_aggregated[Date]) - shows correct number
and they both work in the divide formula as well - as a result, they show change %.
But I want to select month, and show the "MTD" vs "MTD Prev month", for example if now is 05.2023, I want to select 03.2023 and formula should show 03.2023 and other 02.2023 but as the slicer is filtering the whole table on dates only for 03.2023, 02.2023 is filtered out and MTD Last month is blank. how can i prevent a blank value on the previous month?
Solved! Go to Solution.
@PBIlomiddze , Make sure you are using date table, try measure like
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
The main part of the solution was to use Calendar as a filter. more or less the implemented formulas were working - in the end: the solution looks like this.
@PBIlomiddze , Make sure you are using date table, try measure like
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
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 |
---|---|
45 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
73 | |
50 | |
45 | |
20 | |
17 |