Small discrepancies in Total YTD using same measure

[ArchStanton]

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

[ArchStanton]

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]

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]

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

[johnt75]

I don't understand how an inactive relationship can affect anything. Until you specifically activate it using USERELATIONSHIP it is as though it doesn't exist. Are you sure it was inactive ?

[ArchStanton · Accepted solution]

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...

[Anonymous]

Hi, @ArchStanton 

It looks like you have found a solution. Could you please mark this helpful post as “Answered”?

This will help others in the community to easily find a solution if they are experiencing the same problem as you.

Thank you for your cooperation!

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

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]

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]

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]

When I created an inactive relatiionship between Resolution Date and Date2.Date lots of things broke down in my report. Not only that, the measure done this to the black bars:

My data model is already bursting at the seams so I cannot introduce a 2nd date calendar to link Resolution Date to my Date table, this is not going to work.