Calculate the difference in amount from selected start and end date

[cruzp]

Hello pbi community,

I have a sample of records of opps with their stage and when it was changed from different time and the amount.

I need to get the difference from the selected start and end date with some additional criteria if end date stage is Closed Lost. In which examples will be given below.

Scenario#1:

Let's say user selects a specific opportunity,  then selects any date prior or equal to 07/23/2022 as 'Start Date', what it should get is the most recent date prior or equal to 07/23/2022, which would be 07/22/2022 amouting to 230,999.91 based on the data since there is data on it,

Then user selects 09/12/2022 in 'End Date', what it should get is the most recent date prior or equal to 09/12/2022 which would be 09/12/2022 11:09:20PM, amouts to 59,999.88

Hence the expected output:

59,999.88 - 230,999.91 = -171,000.03

Scenario#2:

This is where the selected end date falls on 'Closed Lost' hence will have a criteria that if it was closed lost then value becomes 0 for end date therefore it would be -149,700 in this scenario

It is because Start Date 11/07/2023 will get the value of 11/06/2023 which is 149,700.00

and since the most recent date prior or on the selected end date is Closed Lost, the value becomes 0

Hence the expected calculation output:

0 - 149,700.00 = -149,700.00

 

Here is the link for the sample file:

https://www.dropbox.com/scl/fi/onhnfrukvoga697ejqzdx/Getting-difference-between-two-dates-using-DAX....

[v-jialongy-msft]

Hi @cruzp 

Please try this measure

Difference between EndDate and StartDate = 
VAR SELECTED_StartDate = SELECTEDVALUE('Start Date'[Start Date])
VAR FilteredStartTable = FILTER('FSA','FSA'[EFFECTIVE_TO] >= SELECTED_StartDate)
VAR Next_StartDate = MINX(FilteredStartTable,'FSA'[EFFECTIVE_TO])
VAR StartDate_Amount1 = CALCULATE(SUM('FSA'[DAILY_AMOUNT]),'FSA'[EFFECTIVE_TO] = Next_StartDate)
VAR SELECTED_EndDate = SELECTEDVALUE('End Date'[End Date])
VAR FilteredEndTable = FILTER('FSA','FSA'[EFFECTIVE_TO] <= SELECTED_EndDate)
VAR Next_EndDate = MAXX(FilteredEndTable,'FSA'[EFFECTIVE_TO])
VAR EndDate_Amount1 = CALCULATE(SUM('FSA'[DAILY_AMOUNT]),'FSA'[EFFECTIVE_TO] = Next_EndDate)

RETURN 
IF(SELECTEDVALUE('DIM_STAGE'[STAGE_NAME]) = "Closed Lost",0 - StartDate_Amount1 , EndDate_Amount1 - StartDate_Amount1)

Best Regards,

Jayleny

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

[cruzp]

Hi @v-jialongy-msft, can we use and then fix my version of DAX i created instead of the previous DAX you used?

Here is the DAX to use:

Difference in Amount = 
VAR SELECTED_StartDate = SELECTEDVALUE('Start Date'[Start Date])
VAR SELECTED_EndDate = SELECTEDVALUE('End Date'[End Date])

VAR LatestStartRecord = CALCULATE(MAX(FSA[EFFECTIVE_FROM]),
    FILTER(
        'FSA',
        'FSA'[EFFECTIVE_FROM] <= SELECTED_StartDate
    )
)
VAR StartDate_Amount =
CALCULATE(
    SUM(FSA[DAILY_AMOUNT]),
    FILTER(
        'FSA',
        'FSA'[EFFECTIVE_FROM] = LatestStartRecord
    )
)
VAR LatestEndRecord = CALCULATE(MAX(FSA[EFFECTIVE_FROM]),
    FILTER(
        'FSA',
        'FSA'[EFFECTIVE_FROM] <= SELECTED_EndDate
    )
)
VAR EndDate_Amount =
CALCULATE(
    SUM(FSA[DAILY_AMOUNT]),
    FILTER(
        'FSA',
        'FSA'[EFFECTIVE_FROM] = LatestEndRecord
    )
)
RETURN 
EndDate_Amount - StartDate_Amount

The main problem I am facing in that DAX is that when I filter 

Start Date = 08/15/2022,

The data is retrieving 7/22/2022 6:58:15 AM (230,999.91) which is incorrect, because it should retrieve 8/15/2022 12:30:06 AM (115,000.08)

Same issue with the End Date since I am using the same logic for End Date.

End Date = 08/25/2022,

The data is retrieving 8/15/2022 12:30:06 AM (115,000.08) which is incorrect, because it should retrieve 8/25/2022 12:43:46AM (59,999.88)

The updated file is here:

https://www.dropbox.com/scl/fi/129cs7okbg21ol18buk01/Sample1.pbix?rlkey=7a9bjvrc4imo0w9z44ug3b5nj&dl...

[v-jialongy-msft]

Hi @cruzp 

I've modified your dax and now it's ready for you

Difference in Amount = 
VAR SELECTED_StartDate = SELECTEDVALUE('Start Date'[Start Date])
VAR SELECTED_EndDate = SELECTEDVALUE('End Date'[End Date])

VAR LatestStartRecord = CALCULATE(MIN(FSA[EFFECTIVE_FROM]),
    FILTER(
        'FSA',
        'FSA'[EFFECTIVE_FROM] >= SELECTED_StartDate
    )
)
VAR StartDate_Amount =
CALCULATE(
    SUM(FSA[DAILY_AMOUNT]),
    FILTER(
        'FSA',
        'FSA'[EFFECTIVE_FROM] = LatestStartRecord
    )
)
VAR LatestEndRecord = CALCULATE(MIN(FSA[EFFECTIVE_FROM]),
    FILTER(
        'FSA',
        'FSA'[EFFECTIVE_FROM] >= SELECTED_EndDate
    )
)
VAR EndDate_Amount =
CALCULATE(
    SUM(FSA[DAILY_AMOUNT]),
    FILTER(
        'FSA',
        'FSA'[EFFECTIVE_FROM] = LatestEndRecord
    )
)
RETURN 
EndDate_Amount - StartDate_Amount

Best Regards,

Jayleny

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

[cruzp]

Hi @v-jialongy-msft i believe that would ruin the logic in getting the most recent date prior or equal to the selected date.

for example, you filter 8/16/2022, it should still retrieve 8/15/2022 12:30:06 AM record because it's the most recent date. basically any record that has the most recent date prior or equal to 8/16/2022, same when you select 8/24/2022, it should still retrieve 8/15/2022 12:30:06 AM record because it's the most recent record prior or equal to 8/24/2022.

in the DAX you modified, i am getting 8/25/2022 when 8/16/2022 is filtered.

based on the data that is available, you should only retrieve 8/25/2022 12:43:46 AM when the data selected in slicer is between 8/25/2022 and 8/31/2022 because it's the most recent date prior or equal to those date range.

expected output based on the sample opportunity selected:

when the selected date in start date is < 7/22/2022, it should retrieve no records because there is no data for AMP- DR opp.

when the selected date in start date is => 7/22/2022 and < 8/15/2022 , it should retrieve the record 7/22/2022 6:58:15 AM

when the selected date in start date is => 8/15/2022 and < 8/25/2022 , it should retrieve the record 8/15/2022 12:30:06 AM

when the selected date in start date is => 8/25/2022 and < 9/1/2022 , it should retrieve the record 8/25/2022 12:43:46 AM

[v-jialongy-msft]

Hi @cruzp 

Try the following dax

Difference in Amount = 
VAR SELECTED_StartDate = SELECTEDVALUE('Start Date'[Start Date]) + TIME(12,0,0)
VAR SELECTED_EndDate = SELECTEDVALUE('End Date'[End Date]) + TIME(12,0,0)

VAR LatestStartRecord = CALCULATE(MAX(FSA[EFFECTIVE_FROM]),
    FILTER(
        'FSA',
        'FSA'[EFFECTIVE_FROM] <= SELECTED_StartDate 
    )
)
VAR StartDate_Amount =
CALCULATE(
    SUM(FSA[DAILY_AMOUNT]),
    FILTER(
        'FSA',
        'FSA'[EFFECTIVE_FROM] = LatestStartRecord
    )
)
VAR LatestEndRecord = CALCULATE(MAX(FSA[EFFECTIVE_FROM]),
    FILTER(
        'FSA',
        'FSA'[EFFECTIVE_FROM] <= SELECTED_EndDate 
    )
)
VAR EndDate_Amount =
CALCULATE(
    SUM(FSA[DAILY_AMOUNT]),
    FILTER(
        'FSA',
        'FSA'[EFFECTIVE_FROM] = LatestEndRecord
    )
)
RETURN 
EndDate_Amount - StartDate_Amount

Best Regards,

Jayleny

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

[cruzp]

@v-jialongy-msft worked like a charm!!!! how did you do that?

Can you explain to me how the adjustment made and how it works in getting that output?

[v-jialongy-msft · Accepted solution]

Hi @cruzp 

When you select a date in the slicer, it defaults to showing the time as 12:00:00 AM on that day. However, your requirement is to consider any time that falls on the same date as selected in the slicer as being before that date, without taking into account the specific hours of the day. Therefore, I added 12 hours to the date selected in the slicer

Best Regards,

Jayleny

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