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. Wh...
  • 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.