Forum Discussion
YTD calculation - In month values?
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
4 Replies
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-e3174b39f38aTo 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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
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
