topic Re: YTD FISCAL YEAR not suming up the months correctly in DAX Commands and Tips

YTD FISCAL YEAR not suming up the months correctly

DanielCarvalho — Tue, 21 Mar 2023 15:26:28 GMT

Hey guys,

I Have this folowing measure for tickets created MTD:

Created_Tickets(MTD) = CALCULATE(DISTINCTCOUNT(TICKET_DASHBOARD_GAVATAR_HISTORY[CASEID]),DATESMTD(TICKET_DASHBOARD_GAVATAR_HISTORY[ACTIONDATE_FORMATTED]))

Im trying to do this as YTD and have it as following:

Created_Tickets(YTD) = CALCULATE(DISTINCTCOUNT(TICKET_DASHBOARD_GAVATAR_HISTORY[CASEID]),DATESYTD('CALENDAR'[DATE], "30/9" ))

Im having 2 issues. One is that for the value to be correct I would need to be using the action date column but this is not my column that is connected to calendar date as I need that to be conected to a different date column that is what Im using throughout my analises.

Second issue is the reason im not doing count of action date is because I need it to be based on distinct case ids as they repeat in my data until those same tickets are closed. So the closed tickets YTD is working perfectly with the following measure:

Closed_Tickets(YTD) = CALCULATE(SUM(TICKET_DASHBOARD_GAVATAR_HISTORY[Closed or Not]),DATESYTD('CALENDAR'[DATE], "30/9" ))

Any suggestions?

Re: YTD FISCAL YEAR not suming up the months correctly

johnt75 — Wed, 22 Mar 2023 09:26:41 GMT

Create an inactive relationship from the date table to action date and then use

Created_Tickets(YTD) = CALCULATE ( DISTINCTCOUNT ( TICKET_DASHBOARD_GAVATAR_HISTORY[CASEID] ), DATESYTD ( 'CALENDAR'[DATE], "30/9" ), USERELATIONSHIP ( 'Calendar'[Date], TICKET_DASHBOARD_GAVATAR_HISTORY[ACTIONDATE_FORMATTED] ) )

Re: YTD FISCAL YEAR not suming up the months correctly

DanielCarvalho — Wed, 22 Mar 2023 09:46:20 GMT

Thank you John, this worked. Do you think you can help me with another issue...

I also have a created cumulative visual that is not working due to the same issue...

I tried to apply the same logic and did this measure:

Created Cumulatitive = CALCULATE(DISTINCTCOUNT(TICKET_DASHBOARD_GAVATAR_HISTORY[CASEID]),filter(allselected('CALENDAR'),'CALENDAR'[DATE] <=max('CALENDAR'[DATE])),USERELATIONSHIP(TICKET_DASHBOARD_GAVATAR_HISTORY[ACTIONDATE_FORMATTED],'CALENDAR'[DATE]))

The total of the created cumulative line should match the total of created tickets MTD just as the closed matches? What am I doing wrong?

Kiund Regards,

Daniel

Re: YTD FISCAL YEAR not suming up the months correctly

johnt75 — Wed, 22 Mar 2023 10:50:13 GMT

Its not immediately obvious what is wrong. I think you'd need to dig into the numbers and work out which value is actually correct