Forum Discussion
Automatically calculate a "previous value" based on current slicer selection
- Anonymous1 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.
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.