Help - YTD equation not giving me the answer I expect and I don't know why

[wuvane]

Hi All,

I am very new to Power BI but been very pleased to create something useful quickly. I have used this equation in tables within the same model and it worked fine. For some reason it is now only summarising for one Cluster, I can't see why as I shouldn't have any context filters on the data.

I have checked my relationships which are only set up with two tables, a date table 1:many and a Site table 1:many, these are working for all other measures. I have reloaded the data from scratch, checked the data table, and deleted all equations and started again... what am I missing?!

Thanks in advance, lots of head scratching here!!

[wuvane]

Hi, thanks for the response, no change when I remove it and it works in other calcs...

It feels very odd like a glitch, is there a way to break/check what's driving it?

[ToddChitt]

Why SUMX and not SUM?

What happens if you use the standard TOTALYTD DAX function?

The Cluster should just be dimensionality on the data, should not affect the calculation, only the context of it.

[wuvane]

No reason for SUMX, still trying to understand the language (week 4!!)

I have tried to use the DAX function, not sure if this is right but it then only returns data for a site in the North Cluster, its like the relationships aren't right.

[ToddChitt]

Can you show us the relationships?

[wuvane]

Sure, also just looking again and can't be sure if this has changed but both the Quality and HSER metrics are experiencing the same issue now I have checked. I.e. only the North Cluster is bringing back data. These are the only areas I am doing the calculations for YTD so maybe it is a calculation issue.

[wuvane]

Thanks so much for your help with this, I have found it!! 

I had a table that I didn't realise had a dual directional relationship and it was filtering back for just the North  in a single site onto the rest. Lesson learnt! 

[ToddChitt]

Can we assume that the "30/04" is the year end? And that your regional settings are such that it is able to interpret that? My regional settings would want that to be "4/30". 

What happens if you take that part out? Does it calculate properly for a year ending on December 31?