Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
HI All,
I have a to compare some data based on the selection of the date where the data were created.
Table:
FC on | FC for | Value | ||
01.05.2021 | 01.02.2022 | 100 | ||
01.06.2021 | 01.02.2022 | 99 | ||
01.06.2021 | 01.03.2022 | 150 | ||
01.07.2021 | 01.02.2022 | 105 | ||
01.07.2021 | 01.03.2022 | 85 |
I'm trying to compare the values in two selected "FC on"
e.g. I've selected via Slicer "FC on" 01.06.2021 and 01.07.2021
Desired result is:
FC for | 01.06.2021 | 01.07.2021 | Delta | |||
01.02.2022 | 99 | 105 | 6 | |||
01.03.2022 | 150 | 85 | -65 |
All Data are in one table, didanybody had this problem and might share some hints or a solution to the problem?
Thank you in advance!
Solved! Go to Solution.
Thank you I've adjusted it a bit and placed in in one measure 🙂 works fine!
FC Delta =
VAR _MAX = MAXX(ALLSELECTED(Table),Table[FC on])
VAR _MIN = MINX(ALLSELECTED(Table),Table[FC on])
VAR MinDate = CALCULATE(sum(Table[Value]),FILTER(Table,Table[FC on] = _MIN))
VAR MaxDate = CALCULATE(sum(Table[Value]),FILTER(Table,Table[FC on] = _MAX))
VAR FCDelta = MaxDate - MinDate
return
FCDelta
@Anonymous , You can create three measures and use
Min date =
var _max = maxx(allselected(Table),Table[FC on])
var _min = maxx(allselected(Table),Table[FC on])
return
calculate( sum(Table[Value]), filter('Table', 'Table'[FC on] =_min ))
Max date =
var _max = maxx(allselected(Table),Table[FC on])
var _min = maxx(allselected(Table),Table[FC on])
return
calculate( sum(Table[Value]), filter('Table', 'Table'[FC on] =_max ))
Diff =
divide([Max date] - [Min date], [Min date])
Thank you I've adjusted it a bit and placed in in one measure 🙂 works fine!
FC Delta =
VAR _MAX = MAXX(ALLSELECTED(Table),Table[FC on])
VAR _MIN = MINX(ALLSELECTED(Table),Table[FC on])
VAR MinDate = CALCULATE(sum(Table[Value]),FILTER(Table,Table[FC on] = _MIN))
VAR MaxDate = CALCULATE(sum(Table[Value]),FILTER(Table,Table[FC on] = _MAX))
VAR FCDelta = MaxDate - MinDate
return
FCDelta
User | Count |
---|---|
107 | |
88 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |