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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.