Forum Discussion

ArchStanton's avatar
ArchStanton
Icon for Power Participant rankPower Participant
1 year ago
Solved

Small discrepancies in Total YTD using same measure

Hi,

 

I hope someone can shed some light and help me with this issue, unfortunately I cannot share the pbix for security reasons but hopefully someone can give me one or two pointers as to what is going wrong

 

My TotalYTD measure that gives me 5,017 in my Card Visual which is the correct number

Using the exact same measure in the adjacent Column Chart  the Black Bars add up to 5,019 and not 5,017 (see below).

 

The differences occur in July where the correct figure should be 875, August 822 and Oct 343 compared to the bar chart above.

The measure i'm using is:

Closed Cases YTD = CALCULATE(
    TOTALYTD(COUNT('Cases'[Case Number]),'Cases'[Resolution Date],"31/03"),
    'Cases'[statecode] = "Resolved")

 

I tried to export the 342 in Oct and do a v-lookup in excel again the correct 343 number but it won't let me do it on this bar chart.
I tried changing it to a Matrix and and exporting it that way but it brings 40,000 rows of data.

 

This is bugging me so much - does anyone know why this is behaving like this?

 

Thanks

 

 

Need Help
Show and Tell
  • ArchStanton's avatar
    ArchStanton
    1 year ago

    I'm not sure whats happening either, I inherited this beast of a datamodel over 2 yrs ago. I have so many date columns in my main fact table, as soon as i try to create inactive relationships like you suggest the entire report breaks down. Everything was built on the Created On date > Date2.Date field.
    Anyway, I've split the visual into two and started again - my numbers in black add up to 5,017 and are correct now.

     

     

     

    Thanks for your help, much appreciated...


10 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    1 year ago

    Try creating a proper date table, marked as a date table, and use that in your calculations and visuals. The time intelligence functions can be funny if not using a proper date table.

    • ArchStanton's avatar
      ArchStanton
      Icon for Power Participant rankPower Participant
      1 year ago

      Thanks John, I do have a date table but its not linked to the resolution date, I can't swap that date because if I do, dozens of my other visuals will break. The date table is linked to the Created On date instead

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        1 year ago

        Create an inactive relationship from your date table to the resolution date and then use that relationship in the calculation

        Closed Cases YTD =
CALCULATE (
    TOTALYTD ( COUNT ( 'Cases'[Case Number] ), 'Date'[Date], "31/03" ),
    USERELATIONSHIP ( 'Cases'[Resolution Date], 'Date'[Date] ),
    'Cases'[statecode] = "Resolved"
)
  • ArchStanton's avatar
    ArchStanton
    Icon for Power Participant rankPower Participant
    1 year ago

    Thanks John, I'll give that a go but before I do, I just noticed that a Page Filter that excludes Team L is being ignored in my bar chart. There were 875 closures in July but my bar chart above shows 876, why do you think the page filter seems to be working on one visual but not the other?

    • johnt75's avatar
      johnt75
      Icon for Super User rankSuper User
      1 year ago

      I'm not sure, but something strange appears to be going on. I would expect the YTD value to always increase month-on-month, but it doesn't. It decreases in August then again in October. Try using the code I posted, and change the axis of the graph to the date table and then lets see what the numbers look like.

      • ArchStanton's avatar
        ArchStanton
        Icon for Power Participant rankPower Participant
        1 year ago

        This is not cumulative so the numbers should not always increase. Its the black 'closed' bars that are the problem, the green ones are fine

         

        Ps I cannot create an inactive relationship like you suggest in your code as it breaks multiple visuals and slicers in my report