DAX DAYSYTD not generating expected results

ClaudioCavalli — Thu, 12 Oct 2023 17:07:55 GMT

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.

Re: DAX DAYSYTD not generating expected results

Greg_Deckler — Thu, 12 Oct 2023 17:57:37 GMT

ClaudioCavalli You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

Re: DAX DAYSYTD not generating expected results

ClaudioCavalli — Wed, 18 Oct 2023 21:15:01 GMT

Ok, time to fess up. My formula was working the WHOLE time. I have been banging my head against this problem for three weeks. Here is the problem:

In my Calendar table I have field called MonthNumberFiscal, since our fiscal year starts on 7/1. I was using MonthNumberFiscal to list the months. There is one HUGE problem with this: MonthNumberFiscal is the same across multiple years, so you cannot calculate a YTD number across multiple years.

As soon as I replaced MonthNumberFiscal with the actual date field, the numbers appeared. I figure I would share in case someone runs into a similar problem.

Your YTD functions will not work across multiple years. When you say it like that, it makes sense but it took me three weeks to figure out that is exactly what I was doing.

topic Re: DAX DAYSYTD not generating expected results in DAX Commands and Tips

DAX DAYSYTD not generating expected results

Re: DAX DAYSYTD not generating expected results

Re: DAX DAYSYTD not generating expected results