ALLEXCEPT function showing me all dates on x-axis

[Anonymous]

Hi All, 

I was recently encountering an issue with not being able to correctly filter my graph for cumulative total. I think i've figured out what i need however now, i'm struggling to show the correct dates along my x-axis. 

Initially, i was using ALL function, however, this resulted in my not being able to slice my data using a category slicer. I then used ALLSELECTED, however, because i had a visual filter applied to my graph, it wasn't giving me the correct numbers. At last, i decided i needed 'ALLEXCEPT' as i didn't want my cumulative total to reset.

Now that i have this working, the issue i'm facing is that my x-axis is now showig me all the dates in my main date table. I want to only see the dates that are contained in my Fact table. 

When i was using the ALL function, i was able to achieve this through:
Calculate (['Total']), Filter(ALL('Sheet1'), 'Sheet1[Date'] <= MAX('Sheet1'[Date])

However, since i'm now havig to use 'ALLEXCEPT', when i use this logic, i'm unable to slice my graph which was the problem i was having initially. 

I've attached PBIX file with what i'm currently getting and i've included a table visual to show which dates i should be getting. If anything's unclear, please let me know. 

Thank you!!

[AlB · Accepted solution]

Hi @Anonymous 

I'm a bit lost with what you are trying to do with the ALLEXCEPTs, ALLSELECTEDs etc. One quick option would be to just look at the measure that is giving you results where you want and use that to show the result of your new measure only when the first one is not blank.  See it all at work in the attached file (Page 1)

Cumulative Planned V2 = 
VAR aux_ =
    CALCULATE (
        [Total Planned],
        FILTER ( ALL ( DateT ), DateT[Date] <= MAX ( DateT[Date] ) )
    )
RETURN
    IF ( NOT ISBLANK ( [Cumulative Delivered ALL (VALUE TO STOP AT TODAY)] ), aux_ )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.