Forum Discussion
DatesAdd Function with Custom Date - Help Needed
Hello,
I am using this custom date table and I need to perform some MTD, QTD and YTD calculations. I am using DatesADD function and it just does not seem to work. The result always gives the sum of the whole previous year
https://radacad.com/all-in-one-script-to-create-date-dimension-in-power-bi-using-power-query
Does anyone have any idea how I can perform these activities with a custom date calender?
Best Regards,
Simon
10 Replies
Using DAX you can use the following formulas for YTD and YTD LY
YTD = CALCULATE(SUM(Table6[Amount]),DATESYTD('Calendar'[Date]))
YTD LY = CALCULATE(Table6[YTD],SAMEPERIODLASTYEAR('Calendar'[Date]))
Useful links that you could look at are the following:
https://docs.microsoft.com/en-us/dax/time-intelligence-functions-dax
SimonSeez , Are you using Date table.
Please refer this video where I dicussed Why Time intelligence can fail - https://www.youtube.com/watch?v=OBf0rjpp5Hw
You need make sure 5 things. Please check.
Hello amitchandak
Yes, I am using a date table. Off to watch your video now
SimonSeez , in case that does not help. Also, the useful information provided by other super users/users does not help. then
Please share a sample pbix after removing sensitive data.
Are you using an approach like these?
Calculating MTD, QTD, YTD, Running and Cumulative Total in Power BI (mssqltips.com)
Please share your DAX for a specific suggestion.
Regards,
Pat
Hello Pat,
I am using this
Calculate([Total Revenue], DateAdd('Date Table' [Date], -1, Quarter))
Rather than give me total for the last quarter, it gives a total for the whole of the previous year
