Forum Discussion

RYRY's avatar
RYRY
Frequent Visitor
1 year ago
Solved

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

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

  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

    • RYRY's avatar
      RYRY
      Frequent Visitor
      1 year ago

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