Forum Discussion

ArchStanton's avatar
ArchStanton
Power Participant
3 years ago

TOTALYTD Not Working

Hi,

 

The following YTD code is not working, its 58 when the answer should only be 41 items.

 

I want to count the number of instances where 'Cases'[Total Deferrals] are not ZERO

where [statecode_display] = "Resolved"

 

All Incident IDs are unique identifiers and my Year End Date is 31/3

 

So can anyone tell me why this code is producing a wrong number?

 

When I put the measure into a Matrix visual I had to add the Finanacial Year as a Filter manually so the YTD is obviously the problem.

 

 

YTD Deferrals = CALCULATE(
    TOTALYTD(DISTINCTCOUNT('Cases'[incidentid]),'Cases'[pre_resolutiondate],"31/03"),
    'Cases'[statecode_display] = "Resolved",
    'Cases'[Total Deferrals] <>0)

 

 

 

Thanks

Need Help
Show and Tell

5 Replies

  • MAwwad's avatar
    MAwwad
    Solution Sage
    3 years ago

    The issue with your YTD Deferrals measure is that the TOTALYTD function is not correctly taking into account the filter context of your report. You need to add the 'Cases'[pre_resolutiondate] column to the filter arguments of the TOTALYTD function so that it filters the dates within the current filter context.

    Try modifying your measure as follows:

    YTD Deferrals = CALCULATE( DISTINCTCOUNT('Cases'[incidentid]), FILTER('Cases', 'Cases'[statecode_display] = "Resolved" && 'Cases'[Total Deferrals] <> 0), TOTALYTD('Calendar'[Date], 'Cases'[pre_resolutiondate], "31/03") )

    This measure uses the FILTER function to filter the Cases table to only include rows where 'Cases'[statecode_display] is "Resolved" and 'Cases'[Total Deferrals] is not equal to 0. Then it uses the TOTALYTD function with 'Calendar'[Date] and 'Cases'[pre_resolutiondate] as the arguments to calculate the year-to-date total of distinct incident IDs for the current filter context.

    I hope this helps!

    • ArchStanton's avatar
      ArchStanton
      Power Participant
      3 years ago

      Yes, I've just noticed that as well, thank you for your help and sorry for wasting your time!!

       

      Many thanks

      • MAwwad's avatar
        MAwwad
        Solution Sage
        3 years ago

        Glad I could help, dont forget to accept it as solution to help others

    • ArchStanton's avatar
      ArchStanton
      Power Participant
      3 years ago

      Hi

       

      I would like to try your formula just to see what it produces, however the TOTALYTD required an expression immediately after it, your example uses the Calendar date:

       

      DataSlayer Measure = 
                 CALCULATE( DISTINCTCOUNT('Cases'[incidentid]), 
                 FILTER('Cases', 'Cases'[statecode_display] = "Resolved" && 'Cases'[Total Deferrals] <> 0), 
                 TOTALYTD(

       

      TOTALYTD should be come after Calculate shouyld it not?

      Thanks