Forum Discussion

Majidbhatti's avatar
Majidbhatti
Advocate IV
6 years ago
Solved

Find the difference TODAY minus YESTERDAY data with visual slicer

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 

 

Need Help
  • sturlaws's avatar
    sturlaws
    6 years ago

    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

2 Replies

  • sturlaws's avatar
    sturlaws
    Resident Rockstar
    6 years ago

    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