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


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

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.