I have just started using Time Intelligence functions in DAX and I am running into problems with the DATESYTD function. I have an Access Database that has three queries/tables that I use:
PivotQuery
PivotFTEQuery
Calendar
The two pivot queries have a Date field called Tdate. The Calendar table has a Date field called CaledarDate. THere is a relationship between these tables in Access and I also create a realationship in my PowerPivot model. Now, all transactions that are downloaded in the database have a Day number of 1. So, all transactions in a month happen on the first day of the month.
I also have to deal with a fiscal year starting on July 1st.
The PivotQuery table has a field called Tvalue. I created a first measure as follows:
TvalueSum=CALCULATE(sum(PivotQuery[Tvalue]))
To create an explicit measure for the sum of the transactions’ amounts. This measure works well as it matches the implicit measure “Sum of Tvalue”.
Then I create the TvalueYTD as follows:
TvalueYTD=CALCULATE([TvalueSum],DATESYTD('Calendar'[CalendarDate], “6/30”))
And this formula gives me blanks. If I remove the end of fiscal year parameter, I get the same number in all the months form July to December and blanks in the other months.
The funny thing is that I do not understand where the numbers come from.
Please note that I have data in my DB from 7/1/2021 to 6/1/2024. I have tried to use several dates in the Calendar table:
Dates than more than overlap the dates in use: Specifically, I had dates from 1/1/2021 to 12/31/2021.
Dates that overlap the start date: From 1/1/2021 to 6/30/2021.
Dates that overlap the end date: From 7/1/2021 to 12/31/2021.
Dates that match the start and end dates: From 7/1/2021 to 6/30/2021 (AND from 7/1/2021 to 6/1/2021).
No matter what the formula gives the same results: Blank if I have an end of fiscal year parameter and the unknown number which is the same for each month.
Since I cannot seem to be able to debug the DAX code (I am using only Excel at this time) I created another measure to see why I get the same numbers:
TvalueCount=calculate(COUNTROWS(PivotQuery), DATESYTD('Calendar'[CalendarDate]))
I thought this would give me the number of rows in each month on a YTD basis. Well this formula also gives the same number of rows for each month. I also cannot figure out where this number is coming from.
I thought, well there is a very useful tool that I can use. I can double click on the numbers in the pivot and get the rows that make up that number. So I did so for every month number, and guess what? I add the Tvalues of the rows included and they match each month value, but it does not match the number shown in the pivot. Also each month had a different number of rows, not the same number that is shown in the pivot.
Finally, this works with the blank rows. If I run the measure with the “06/30” end of year parameter I will get the blank results. But if I double click on each of the blank results I will get the same rows (one by one) as if I remove the parameter.
Where should I look to resolve this problem?
P.S. Somembody mentioned on the Excel forum that my date format might be incorrect so I tried both "6/30" or "6-30" and the results are the same.
Also what do I need to debug DAX statements? Can I export the model so I can upload it somewhere or import it in another worksheet?
Thank you for any help. I have been looking for an answer of the web, but so far I came up empty.
