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.
Hi,
I am struggling with the following case.
I have a datetable and a factstable:
factstable:
Value from to
2 7-10-2019 31-12-2019
2 14-10-2019 01-02-2020
2 21-10-2019 01-02-2020
3 22-10-2019 31-12-2019
all dates between 7-10 and 14-10 should show value 2 and value between 7-10 and 21-10 should show value 2+2 = 4
and all dates after 21-10 should show a sum of all values. AND dates after 31-12 and before 01-02-2020 should be the sum of 2 (14-10) + 2 (22-10) = 4 , so since values to 31-12 are already ended.
To put it more simple: show cumulative values if date is after FROM date and before TO date.
Solved! Go to Solution.
Hi Coan7,
You could try below measure to see whether it work or not.
Measure 2 = IF ( MIN ( 'datetable'[Date] ) > MAXX ( ALL ( factable ), factable[from] ), CALCULATE ( SUM ( factable[value] ), FILTER ( ALL ( factable ), MIN ( 'datetable'[Date] ) >= factable[from] && MIN ( 'datetable'[Date] ) < factable[to] ) ), CALCULATE ( SUM ( factable[value] ), FILTER ( ALL ( factable ), MIN ( 'datetable'[Date] ) >= factable[from] ) ) )
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Coan7,
You could try below measure to see whether it work or not.
Measure 2 = IF ( MIN ( 'datetable'[Date] ) > MAXX ( ALL ( factable ), factable[from] ), CALCULATE ( SUM ( factable[value] ), FILTER ( ALL ( factable ), MIN ( 'datetable'[Date] ) >= factable[from] && MIN ( 'datetable'[Date] ) < factable[to] ) ), CALCULATE ( SUM ( factable[value] ), FILTER ( ALL ( factable ), MIN ( 'datetable'[Date] ) >= factable[from] ) ) )
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
SWITCH( TRUE(), IF(DATE >= 7-10-2019 && DATE <= 31-12-2019,
2, IF(DATE >= 7-10-2019 && DATE <= 31-12-2019,
4, (Final Value if none of this is valid)
Hi @Anonymous ,
Try a SWITCH, just fill in the IF values, and the return value.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
User | Count |
---|---|
88 | |
88 | |
75 | |
67 | |
58 |
User | Count |
---|---|
136 | |
110 | |
91 | |
84 | |
69 |