Calculating total for previous day before the latest filtered day

[Anonymous]

Hi, 

I am trying to find a way to show the total of the latest filtered day and the total the day before. I know how to do this with TODAY and YESTERDAY functions but it seems to be a bit more complicated with dynamically changing dates.

I succeeded to get the total of the latest filtered day by using this measure:

Total of selected day= CALCULATE(

SUM(Table[value]),

FILTER( ALL(Table), Table[date] = Max(Table[date])))

This is the measure I tried to use to get the total of the previous day but it is not working as wanted:

Total of selected day= CALCULATE(

SUM(Table[value]),

FILTER( ALL(Table), Table[date] = Max(Table[date])-1))

I'm very grateful for every tip. Thanks in advance!

Julia

[AlB · Accepted solution]

@Anonymous 

The problem seems to be that the date column is of type Datetime rather than date, with non-zero times. That would be why, when comparing to MAX(Table[Date]) - 1 in the filter operation, the equality doesn't hold. Try this:

Day before  V2 =
CALCULATE (
    SUM ( 'Table'[value] ),
    FILTER (
        'Table',
        DATEVALUE ( 'Table'[date] )
            = DATEVALUE ( MAX ( 'Table'[date] ) - 1 )
    )
)

Please mark the question solved 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.

Cheers 

[Anonymous]

Hi,

You can download an example file from here:

https://drive.google.com/file/d/1VIWjztF61R834Vm5IfHo-BlK7crZ89EE/view?usp=sharing 

I'm trying to compare the two measures in a line chart. The "Last day" measure is working perfectly while the "Previous day" measure is showing a blank box.

Thank you for your help!

Julia

[AlB]

Hi @Anonymous 

Your code looks good. What is it that is not working exactly? Can you show some sample with the expected result and the (erroneous) result you currently get? Or best if you can share the pbix

Please mark the question solved 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.

Cheers