Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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:
Solved! Go to 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.
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.
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...
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
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:
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
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.
hi @v-junyant-msft thank you so much, it worked! can you just explain the whole DAX to me? for my understanding please.
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
hello @v-junyant-msft make sense! thank you so much Dino. I appreciate your help!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |