Forum Discussion
YTD formula does not work
Hi! I want to calculate fiscal YTD sales. I created a calander table based on min and max dates of sales table. Then I joined the sales and caladar table. My fiscal year ends in 30th Sep.
I used below DAX:
YTD Sales = CALCULATE(SUM(Sales[Sales]),DATESYTD('Calendar Table'[Date],"30/9"))
But colums gives same values as summation. Not running total.
Please help! ")
Hi Anonymous
Your DAX code for YTD Sales itself looks fine.
Here are a couple of thinkgs to check, given that distinct values of Date in your screenshot all appear with the same formatted value "Monday, October 01 2018".
- Was the Sales[Date] column loaded as Date/Time, then the column type changed to Date after loading the table?
If so, that column will still store Date/Time values (even if formatted as Date values), and the relationship with 'Calendar Table'[Date] won't work as intended.
Ideally, change the column type to Date in Power Query so it is loaded as a date with no time component.
If Time values are still required, store the Time or full Date/Time value in another column.
Time intelligence calculations using both date & time are a bit more complicated and would require a Time dimension table as well. - Are you using Sales[Date] on the table visual, rather than 'Calendar Table'[Date]?
For time intelligence functions to work properly, the Date column from the date table 'Calendar Table' should be used in visuals for grouping/filtering. Also, as a general rule, the Date column from Sales should be hidden and not used in the report.
Regards,
Owen
- Was the Sales[Date] column loaded as Date/Time, then the column type changed to Date after loading the table?
1 Reply
Hi Anonymous
Your DAX code for YTD Sales itself looks fine.
Here are a couple of thinkgs to check, given that distinct values of Date in your screenshot all appear with the same formatted value "Monday, October 01 2018".
- Was the Sales[Date] column loaded as Date/Time, then the column type changed to Date after loading the table?
If so, that column will still store Date/Time values (even if formatted as Date values), and the relationship with 'Calendar Table'[Date] won't work as intended.
Ideally, change the column type to Date in Power Query so it is loaded as a date with no time component.
If Time values are still required, store the Time or full Date/Time value in another column.
Time intelligence calculations using both date & time are a bit more complicated and would require a Time dimension table as well. - Are you using Sales[Date] on the table visual, rather than 'Calendar Table'[Date]?
For time intelligence functions to work properly, the Date column from the date table 'Calendar Table' should be used in visuals for grouping/filtering. Also, as a general rule, the Date column from Sales should be hidden and not used in the report.
Regards,
Owen
- Was the Sales[Date] column loaded as Date/Time, then the column type changed to Date after loading the table?