Retrieve the most recent records prior or on the selected slicer date

[cruzp]

I have a sample data below and I want to retrieve a record of opportunities based on a slicer date.

How do i create a date slicer that will do the following below?

Opportunity	STAGE	EFFECTIVE_TO	DAILY_AMOUNT	EFFECTIVE_FROM
443ID - ISV	Qualifying	12/16/2021	$ 99,999	11/30/2021
443ID - ISV	Validating	01/11/2022	$ 49,999	12/16/2021
443ID - ISV	Best Case	02/07/2022	$ 49,999	01/11/2022
443ID - ISV	Best Case	02/17/2022	$ 56,500	02/07/2022
443ID - ISV	Closed Won	12/30/2900	$ 56,500

THE GOAL is when User selects a slicer of Starting Date for e.g 13th of February.

It will retrieve the record with MAX of EFFECTIVE TO date:

Best Case

02/07/2022

$ 49,999

This is the latest position as at 13th of February, so becomes the starting position.

Compare to ending position, where EFFECITVE_TO = 12/30/2900

Closed Won

12/30/2900

$ 56,500

And then create measure to compare the 2 position, to return a net increase in $$ of $6,501 

because 56,600 - 49,999 = $6,501

here is the sample .pbix:

https://www.dropbox.com/scl/fi/40nbw2plx9w5sisc2c7s7/sample.pbix?rlkey=5isa1ohhtej5nikoy2tuibxus&dl=...

[v-junyant-msft]

Hi @cruzp 

Please try this way:
I create such a new table to create slicer:

Then use this DAX to create a new measure:

Difference = 
VAR SELECTED_Date = SELECTEDVALUE(Slicer_Date[Date])
VAR NEXT_Date = 
CALCULATE(
    MIN('FSA'[EFFECTIVE_TO]),
    FILTER(
        ALL(FSA),
        'FSA'[EFFECTIVE_TO] > SELECTED_Date
    )
)
VAR CURRENT_Amount =
CALCULATE(
    SUM(FSA[DAILY_AMOUNT]),
    FILTER(
        ALL(FSA),
        'FSA'[EFFECTIVE_TO] = NEXT_Date
    )
)
VAR CLOSED_Amount =
CALCULATE(
    SUM(FSA[DAILY_AMOUNT]),
    FILTER(
        ALL(DIM_STAGE),
        'DIM_STAGE'[STAGE_NAME] = "Closed Won"
    )
)
RETURN
CLOSED_Amount - CURRENT_Amount

The final output is like below:

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

[cruzp]

hi @v-junyant-msft i tried applying it using updated data from our database, but why it would not work on my end.

1/13/2022 is the selected date in slicer. however, the difference is not the same as yours as i have -143.50k

can you able to fix it using the file below?

i am using this file:
https://www.dropbox.com/scl/fi/kah4fxum3b8gtjvrhoex1/Opportunity-Movement_Sample.pbix?rlkey=y1jiwkt6...

[v-junyant-msft]

Hi @cruzp ,

I tested it with my colleague and this DAX didn't have problems during our testing and I checked the pbix you provided the second time and there is no problem in terms of data modeling, the only difference is in the data. So I suspect that the result derived in your pbix should probably be this answer, but I have no way to verify it because you have too much data.

As far as this side of the aggregated data is concerned the results are fine.

Best Regards,
Dino Tao

[cruzp]

hi @v-junyant-msft ,

can you able to adjust or fix the DAX to get the desired numbers using the file here which contains the updated data:

https://www.dropbox.com/scl/fi/xxv5uw4m0pons0nlgv4tw/Opportunity-Movement_Samplev2.pbix?rlkey=1otv4k...

Example 1:

When 02/18/2022 is selected in date slicer, the difference should be $0 from the ending position 12/30/2900 (Closed Won) since the most recent date prior or on the selected date in slicer is the record 02/17/2022 (Best Case) which value is $56,500.08

In the .pbix, it shows 875,391,518.77

Example 2:

When any date between  02/07/2022 and 02/16/2022 is selected in date slicer, the difference should be $6,500.13 from the ending position 12/30/2900 (Closed Won) since the most recent date prior or on the selected date in slicer is the record 02/07/2022 (Best Case) which value is $49,999.95

In the .pbix, it shows 875,663,874.90

Example 3:

When any date prior to 1/11/2022 is selected in date slicer, the difference should be $-43,499.82 from the ending position 12/30/2900 (Closed Won) since the most recent date prior or on the selected date in slicer is the record 12/16/2021 (Qualifying) which value is $99,999.90

can you able to follow?

thanks a lot

[v-junyant-msft · Accepted solution]

Hi @cruzp ,

Maybe I know what was the problem with the previous DAX, you were applying a page level filter, while I was testing a visual object level filter, and I was using the "ALL" function in my DAX, which ignores the filter.
Please try this DAX:

Difference = 
VAR SELECTED_Date = SELECTEDVALUE(Slicer_Date[Date])
VAR NEXT_Date = 
CALCULATE(
    MAX('FSA'[EFFECTIVE_TO]),
    FILTER(
        // ALL(FSA),
        'FSA',
        'FSA'[EFFECTIVE_TO] < SELECTED_Date
    )
)
VAR CURRENT_Amount =
CALCULATE(
    SUM(FSA[DAILY_AMOUNT]),
    FILTER(
        // ALL(FSA),
        'FSA',
        'FSA'[EFFECTIVE_TO] = NEXT_Date
    )
)
VAR CLOSED_Amount =
CALCULATE(
    SUM(FSA[DAILY_AMOUNT]),
    FILTER(
        // ALL(DIM_STAGE),
        'DIM_STAGE',
        'DIM_STAGE'[STAGE_NAME] = "Closed Won"
    )
)
RETURN
CLOSED_Amount - CURRENT_Amount

The final output is below:

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

[cruzp]

hi @v-junyant-msft thank you so much, it worked! can you just explain the whole DAX to me? for my understanding please.

[v-junyant-msft]

Hi @cruzp ,

I've labeled for you in "/*      */" what each DAX segment is for:

Difference = 

/*Customize a function called SELECTED_Date to get the date selected in Slicer*/
VAR SELECTED_Date = SELECTEDVALUE(Slicer_Date[Date])

/*Customize a function called NEXT_Date to find the largest date in the table that is before the selected date.*/
VAR NEXT_Date = 
CALCULATE(
    MAX('FSA'[EFFECTIVE_TO]),
    FILTER(
        'FSA',
        'FSA'[EFFECTIVE_TO] < SELECTED_Date
    )
)

/*Customize a function called CURRENT_Amount to calculate the Daily Amount for Next_Date.*/
VAR CURRENT_Amount =
CALCULATE(
    SUM(FSA[DAILY_AMOUNT]),
    FILTER(
        'FSA',
        'FSA'[EFFECTIVE_TO] = NEXT_Date
    )
)

/*Customize a function called CLOSED_Amount to calculate the Daily Amount for "Closed Won".*/
VAR CLOSED_Amount =
CALCULATE(
    SUM(FSA[DAILY_AMOUNT]),
    FILTER(
        'DIM_STAGE',
        'DIM_STAGE'[STAGE_NAME] = "Closed Won"
    )
)

RETURN

/*Calculate the difference*/
CLOSED_Amount - CURRENT_Amount

Best Regards,
Dino Tao 

[cruzp]

hello @v-junyant-msft make sense! thank you so much Dino. I appreciate your help!