topic Re: Automatically calculate a "previous value" based on current slicer selection in DAX Commands and Tips

Automatically calculate a "previous value" based on current slicer selection

RYRY — Tue, 07 Jan 2025 22:37:53 GMT

Let's say, I have two slicers:

1. Days of the Week (Monday through to Sunday in that order)

2. Previous Day of the Week (same as above)

Both slicers have seven values each and are identical. What I am wanting to achieve is to eliminate the need of the second slicer by only using the first one. If I select a value on the first slicer, the "previous day" should be selected on the second slicer.

I am using these slicers days to calculate something, so ideally, I'd like to not have to use a second slicer but reference a measure (or some calulated value) that is based on the first slicer.

Here is my attempt at a calculate function but I get a syntax error.

Submitted Applications Previous Day = CALCULATE(

COUNT(DIM_Applicant[app_id]),

'DIM_Applicant'[app_status] = "Active",

REMOVEFILTERS(),

DIM_Applicant[day_submitted] = [Previous Day]

)

My [previous day] is a measure I created like so

Previous Day =

SELECTEDVALUE('DIM_Day'[day_of_week]) & SELECTEDVALUE('DIM_Day'[day_number]) -1

My error is

A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Thank you

Re: Automatically calculate a "previous value" based on current slicer selection

lbendlin — Tue, 07 Jan 2025 23:12:22 GMT

use + instead of &

Re: Automatically calculate a "previous value" based on current slicer selection

RYRY — Tue, 07 Jan 2025 23:26:14 GMT

My [previous day] measure works out with no errors. It's when referencing this measure in the calculate function it gets the synrax error.

Re: Automatically calculate a "previous value" based on current slicer selection

Anonymous — Wed, 08 Jan 2025 02:17:22 GMT

Thanks for lbendlin's concern about this issue.

Hi, RYRY

Since you did not give me some test data to test, I assumed some data myself:

DIM_Applicant:

DIM_day:

I will provide some steps below which I hope will help you:
First, create a calculated column in the DIM_day table:

Previous Day of Week = SWITCH( 'DIM_Day'[day_of_week], "Monday", "Sunday", "Tuesday", "Monday", "Wednesday", "Tuesday", "Thursday", "Wednesday", "Friday", "Thursday", "Saturday", "Friday", "Sunday", "Saturday" )

Then create the following three Measure in order:

Selected Day = SELECTEDVALUE('DIM_Day'[day_of_week])

Previous Day = CALCULATE( MAX('DIM_Day'[Previous Day of Week]), FILTER( DIM_Day, 'DIM_Day'[day_of_week] = [Selected Day] ) )

Submitted Applications Previous Day = CALCULATE( COUNT(DIM_Applicant[app_id]), 'DIM_Applicant'[app_status] = "Active", FILTER( ALL(DIM_Applicant), DIM_Applicant[day_submitted] = [Previous Day] ) )

Select Card visual and drag Measure Submitted Applications Previous Day into Card visual:

I have attached the pbix file for this example below, I hope it helps!

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.