Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
Can someone help me please?
I'm brand new to PBI and I'm looking for a simple YTD calculation however, the data I have is already cumulative?
I'm aware that there is a YTD calculation but that does not work for me because my data is cumulative.
So, in my head, I need say, Julys result i.e. 500.
I need to know Januarys result i.e. 200.
(Jul) 500- (Jan) 200=(YTD)300.
Can someone give me any pointers on how to acheive this please?
Any tips would be most appreciated?
Thanks in adavnce for your help with this.
Cheers
@john_whelan , Hope I got it correctly
Try with Date table. Example
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Power BI — YTD Questions — Time Intelligence 1–5
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
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.
Thanks for the quick reply Amit.
That solution would work if the data was not cumulative.
Am I taking the correct approach by trying to select the 'cumulative' result at the end of last year, then the variance between that and today is the result I'm looking for?
Thanks again for your help.
@john_whelan , so you have data only in last year dec, or every month you have YTD data.
If every month has YTD data - then use MTD
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
if only dec has all data
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
//12/31 represent year end. So if have different year end use that
Thanks so much for all your help. It didn't answer the question exactly but gave a way to the solution.
The issue was because the data is Cumulative - Not in Month.
Essentially I used,