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 All,
Im trying to create a DAX formula for MTD for previous year.
I want to insert this formula to a Pivot Table in PowerBI.
I have used multiple formulas found from various websites but with no lack.
The Pivot table has one dimension for rows and the Year-Month (from Custom Calendar) as columns.
For current month im trying to create a formula for MTD previous year.
All formulas that i have created bring the total for same month from previous year.
For all completed months (e.g. Jan to Mar the data are correct).
Im wondering whether the use of PivotTable on PowerBI has to do with this issue.
At the moment im working on this formula:
CALCULATE(SUM('Invoices to Clients'[TurnOver]), DATESBETWEEN('Calendar'[Date], FIRSTDATE(DATEADD('Calendar'[Date],-12,MONTH)), LASTDATE(DATEADD('Calendar'[Date],-12,MONTH)) ) )
Solved! Go to Solution.
Next time i will try to send a sample dataset.
I tried this but with no luck. I am a bit surprised why this formula didnt work.
I was the last person who replied to this thread and i provided the following formula. I used the EDATE function.
Turnover MTD (PY) = CALCULATE(SUM('Invoices to Clients'[TurnOver]), DATESBETWEEN('Calendar'[Date], FIRSTDATE(DATEADD('Calendar'[Date],-12,MONTH)), IF(MONTH(MIN('Calendar'[Date])) = MONTH(TODAY()), EDATE(TODAY(),-12) , LASTDATE(DATEADD('Calendar'[Date],-12,MONTH))) ) )
Next time i will try to send a sample dataset.
I tried this but with no luck. I am a bit surprised why this formula didnt work.
I was the last person who replied to this thread and i provided the following formula. I used the EDATE function.
Turnover MTD (PY) = CALCULATE(SUM('Invoices to Clients'[TurnOver]), DATESBETWEEN('Calendar'[Date], FIRSTDATE(DATEADD('Calendar'[Date],-12,MONTH)), IF(MONTH(MIN('Calendar'[Date])) = MONTH(TODAY()), EDATE(TODAY(),-12) , LASTDATE(DATEADD('Calendar'[Date],-12,MONTH))) ) )
Hi @themistoklis,
Please check whether this thread helpful to your scenario: Compare MTD with previous period
Regards,
Yuliana Gu
Hi,
How about this one
=CALCULATE(SUM('Invoices to Clients'[TurnOver]),SAMEPERIODLASTYEAR(Calendar[Date]))
Sample/example data please. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |