Finding Difference to Previous value in selected date (filter contex)

[aizhan01]

Here is my table 

Category	File Date	Applicants
Car	23/08/2023	1452
Car	30/08/2023	739
Car	6/09/2023	1096
Car	13/09/2023	1503
Bike	23/08/2023	5246
Bike	30/08/2023	3839
Bike	6/09/2023	5126
Bike	13/09/2023	6398

am trying to calculate Difference to previous date, applying ALLSELECTED() in my DAX to use filter context but ignore row context for previous date. However it doesn't work. 

DAX: 
[Previous Date] = VAR _thisdate =  MAX(table[File Date])
              RETURN calculate(  MAX(table[File Date]), ALLSELECTED(table[File Date]) ,table[File Date] < _thisdate ) 

First, when i use the measure in a new calculation below  :
     CALCULATE( SUM(table[Applicants]),  table[File Date]  = [Previous Date])

it returns error : a function PLACEHOLDER has been used in True/False expression that is used as a table filter expression.

So then i have to redefine everything in the measure :

[Prev.dateApplicant] = VAr _thisdate = MAX(table[File Date])
          Var _daybefore = calculate(  MAX(table[File Date]), ALLSELECTED(table[File Date]) ,table[File Date] < _thisdate ) 

          RETURN CALCULATE( SUM(table[Applicants]), table[File Date] = _daybefore)

[Difference to Prev.Date] = sum(table[Applicants])-[Prev.dateApplicant]

But the result is not correct when i apply filter of File Date to select only  3 dates: 23/08/2023, 6/09/2023, 13/09/2023

Result:

	Applicants	Previous Date	Prev.dateApplicant	Difference to Prev.Date
Category	23/08/2023	6/09/2023	13/09/2023	23/08/2023	6/09/2023	13/09/2023	23/08/2023	6/09/2023	13/09/2023	23/08/2023	6/09/2023	13/09/2023
Car	1452	1096	1503	16/09/2023	30/08/2023	6/09/2023	1107	739	1096	345	357	407
Bike	5246	5126	6398	16/09/2023	30/08/2023	6/09/2023	4318	3839	5126	928	1287	1272

I want this: 

Applicants

Previous Date

Prev.dateApplicant

Difference to Prev.Date

Category

23/08/2023

6/09/2023

13/09/2023

23/08/2023

6/09/2023

13/09/2023

23/08/2023

6/09/2023

13/09/2023

23/08/2023

6/09/2023

13/09/2023

Car

1452

1096

1503

23/08/2023

6/09/2023

1452

1096

-356

407

Bike

5246

5126

6398

23/08/2023

6/09/2023

5246

5126

-120

1272

What is wrong with my DAX, can someone give me the correct DAX to get what i need please?

thanks

[v-jianpeng-msft · Accepted solution]

Thank you Sahir_Maharaj and Sahir_Maharaj 

Hi, @aizhan01 

According to your description, I use the dataset you provided:

First, create a calculation table using the following dax expression:

Table 2 = VALUES('Table'[File Date])

In the second step, use to create the relationship as shown in the figure:

In the third step, create the following two measure:

PreviousDay = 
VAR _seleted_value = VALUES('Table 2'[File Date])
RETURN 
CALCULATE(MAX('Table'[File Date]),FILTER(ALL('Table'[File Date]),'Table'[File Date] < MAX('Table'[File Date])&&'Table'[File Date] IN _seleted_value))

Difference = 
VAR _preday = [PreviousDay]
VAR _current_day_applicant = CALCULATE(SUM('Table'[Applicants]),FILTER(ALL('Table'[File Date]),'Table'[File Date] = MAX('Table'[File Date])))
VAR _pre_applicant = CALCULATE(SUM('Table'[Applicants]),FILTER(ALL('Table'[File Date]), 'Table'[File Date] = _preday))
RETURN _current_day_applicant - _pre_applicant 

Use Table 2 to create a slicer. The Table creates a matrix as follows:

Select the corresponding date to get the output you expect:

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sahir_Maharaj]

Hello @aizhan01,

Can you please try this approach:

1. Calculate the Previous Date

Previous Date = 
VAR _thisDate = MAX(table[File Date])
RETURN
    CALCULATE(
        MAX(table[File Date]),
        FILTER(
            ALLSELECTED(table),
            table[File Date] < _thisDate && table[Category] = EARLIER(table[Category])
        )
    )

2. Calculate Applicants for the Previous Date

Prev.dateApplicant = 
VAR _prevDate = [Previous Date]
RETURN
    CALCULATE(
        SUM(table[Applicants]),
        table[File Date] = _prevDate,
        VALUES(table[Category])
    )

3. Difference to Previous Date

Difference to Prev.Date = 
SUM(table[Applicants]) - [Prev.dateApplicant]

Hope this helps.

[rajendraongole1]

Hi @aizhan01 - can you modify the previous date calculation created as below, using REMOVEFILTERS function. 

Previous Date =

VAR _currentDate = MAX(ApplicantsTable[File Date])

RETURN CALCULATE(

    MAX(ApplicantsTable[File Date]),

    REMOVEFILTERS(ApplicantsTable[File Date]),   -- Ignore all filters on the date

    ApplicantsTable[File Date] < _currentDate    -- Get the date before the current date

)

create the previous Date Applicants Calculation

Prev.dateApplicant =

VAR _previousDate = [Previous Date]

RETURN CALCULATE(

    SUM(ApplicantsTable[Applicants]),

    REMOVEFILTERS(ApplicantsTable[File Date]),  -- Ignore filters again to get correct applicants for the previous date

    ApplicantsTable[File Date] = _previousDate

)

Now calculates the difference between the current applicants and the applicants on the previous date.

Difference to Prev.Date = SUM(ApplicantsTable[Applicants]) - [Prev.dateApplicant]

[aizhan01]

hi @rajendraongole1   

Thank you for your reply. I tried the above calculation and it's not giving want i want as it is removing the filter context.

For reference my calculation gives correct answer for 13Sept2023 but wrong for 6 Sep2023 because my filter excludes 30Aug2023 data, and I want previous date to only consider selected dates, i.e 23Aug2023 here.