Date Slicer Magic! Please help!

[littlemojopuppy]

Hi!  I've encountered something truly unusual and hoping that someone might be able to provide an explanation and how to correct for this.

Trying to calculate a cumulative total during the selected time period.  The formula is almost exactly what you'd expect it to be and it works correctly as shown in the screen snip below the code.

Contract Net Change Cumulative Total:=
VAR WeeksEnding =
    CALCULATETABLE (
        VALUES ( 'Calendar'[CalendarWeekEnd] ),
        REMOVEFILTERS ( 'Calendar'[CalendarWeekEnd] )
    )
RETURN
    CALCULATE (
        [Weekly Contract Net Change],
        FILTER (
            WeeksEnding,
            'Calendar'[CalendarWeekEnd]
                <= MAX ( 'Calendar'[CalendarWeekEnd] )
        )
    )

HOWEVER if I change the date slicer from relative date to on/after date slicer, I get very different results...

Not only is it starting at the most recent date and working backwards, it appears that the addition/subtraction is reversed!  😮😮😮

Has anyone ever encountered this?  Help?

[v-yingjl]

Hi @littlemojopuppy ,

Looks like strange. I have created the same formula which data is from SSAS but could not reproduce it in my side, the formula works well whether the slicer changes or not.

Attached the test sample in the below.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[littlemojopuppy]

Hi @v-yingjl thanks for looking at it.  It's the strangest thing I've ever seen!

[parry2k]

@littlemojopuppy sounds good.

[littlemojopuppy]

Hi @parry2k I created a sample pbix of the data model.  And when I recreated the visualization, I was unable to duplicate the issue.  Furthermore, when I went back to dig deeper into this my SSAS data model did not behave the same way - all without changing any measures or the data model (other than refreshing data).  This is very, very strange...

Very curious if anyone else has encountered any similar issues.

[AllisonKennedy]

So classic case of close and restart fixed this one for now? I'll keep an eye out if I can reproduce, but haven't come across this yet to date.

[littlemojopuppy · Accepted solution]

Hi @AllisonKennedy I don't know if I'd call it "fixed" but I havent't been able to reproduce it and I haven't changed the formula at all.  Strangest thing I've ever seen!

[parry2k]

@littlemojopuppy I tried on my simple data and unable to produce what you described here. Can you share pbix file to look into it? Logically it should work but as you clearly showed in your 2nd screenshot it is not doing what it is expected to do, so I would like to look at the pbix to investigate it further.

[littlemojopuppy]

Hi @parry2k  thank you for taking a look.

The actual data is in a SSAS model but I'll try to mock something up and will send.

[littlemojopuppy]

Hi @VijayP I don't think there's a problem with the formula.  It's that the results change depending on which kind of date slicer is used...

[VijayP]

@littlemojopuppy 

I wonder if you use ALLSELECTED after Filter if it would help!

[littlemojopuppy]

@parry2k  @AllisonKennedy you two are really good 🙂  Any idea why the results of the formula change when the slicer is changed???