Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DanielCarvalho
Helper III
Helper III

YTD FISCAL YEAR not suming up the months correctly

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? 
1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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] )
)

View solution in original post

3 REPLIES 3
johnt75
Super User
Super User

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] )
)

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]))
DanielCarvalho_0-1679478321647.png

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

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors