Find the difference TODAY minus YESTERDAY data with visual slicer

[Majidbhatti]

In the below matrix, I am trying to find the difference TODAY minus YESTERDAY data. So in the below example, when user selects AS ON date which is 4 Nov, I have to show the difference in the forecast TODAY minus YESTERDAY for DEC 2019.

I am able to get the Totals of 4 Nov in the matrix but I am not able to get the YESTERDAYS data as the matrix is being filtered on the 4 Nov. My measure is as follows. I am not able to exclude the filter. I know there is a way to go in "Edit Interactions" and exclude but i want formula based exclusion. 

M3 =

VAR PMSdate = SELECTEDVALUE('OTB Snapshots'[PMSDate].[Date], TODAY())

Var ValDiff =

            Sum('OTB Snapshots'[Room Nights - OTB]) -

              CALCULATE( Sum('OTB Snapshots'[Room Nights - OTB]),

                  FILTER( 'OTB Snapshots',

                      'OTB Snapshots'[PMSDate].[Date] = PMSdate - 1

                    )

              )

Return

     ValDiff 

[sturlaws · Accepted solution]

Hi @Majidbhatti 

could you try this code:

M3 =
VAR PMSdate =
    SELECTEDVALUE ( 'OTB Snapshots'[PMSDate].[Date], TODAY () )
VAR ValDiff =
    SUM ( 'OTB Snapshots'[Room Nights - OTB] )
        - CALCULATE (
            SUM ( 'OTB Snapshots'[Room Nights - OTB] ),
            FILTER (
                ALL ( 'OTB Snapshots'[PMSDate] ),
                'OTB Snapshots'[PMSDate] = PMSdate - 1
            )
        )
RETURN
    ValDiff

Cheers,
Sturla

[Majidbhatti]

Bravo, its working fine ...