Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear Community,
I have one slicer with Canlendar Month values. This slicer should return values where "Updated Month"="Created Month"="Slicer Month" value OR if "Updated Month"=Slicer then return sum of values. Can someone give some advice?
Solved! Go to Solution.
Hi @zhandos
It is better for you to build a unrelated date table, or your Data table will be filtered by date table due to relationship, then return wrong result.
Date Table:
Calendar =
ADDCOLUMNS (
CALENDAR ( DATE ( 2021, 01, 01 ), DATE ( 2021, 12, 31 ) ),
"Year", MONTH ( [Date] ),
"Month", MONTH ( [Date] ),
"MonthYear", FORMAT ( [Date], "MMM-YY" ),
"YearMonth",
YEAR ( [Date] ) * 100
+ MONTH ( [Date] )
)
amitchandak 's code will return 15 if you select Sep-21. Try my code.
Measure =
VAR _SelectMonthYear = SELECTEDVALUE('Calendar'[MonthYear])
VAR _TID = CALCULATETABLE(VALUES(Data[ID]),FILTER(ALL(Data),Data[Updated Month] = _SelectMonthYear))
return
IF(
AND(MAX(Data[Created Month]) = _SelectMonthYear,MAX(Data[Updated Month]) = _SelectMonthYear),SUM(Data[Value]),
IF(MAX(Data[Updated Month]) = _SelectMonthYear,SUMX(FILTER(ALL(Data),Data[ID] in _TID),Data[Value]),
BLANK())
)
Result is as below.
Select Aug-21:
Select Sep-21:
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @zhandos
It is better for you to build a unrelated date table, or your Data table will be filtered by date table due to relationship, then return wrong result.
Date Table:
Calendar =
ADDCOLUMNS (
CALENDAR ( DATE ( 2021, 01, 01 ), DATE ( 2021, 12, 31 ) ),
"Year", MONTH ( [Date] ),
"Month", MONTH ( [Date] ),
"MonthYear", FORMAT ( [Date], "MMM-YY" ),
"YearMonth",
YEAR ( [Date] ) * 100
+ MONTH ( [Date] )
)
amitchandak 's code will return 15 if you select Sep-21. Try my code.
Measure =
VAR _SelectMonthYear = SELECTEDVALUE('Calendar'[MonthYear])
VAR _TID = CALCULATETABLE(VALUES(Data[ID]),FILTER(ALL(Data),Data[Updated Month] = _SelectMonthYear))
return
IF(
AND(MAX(Data[Created Month]) = _SelectMonthYear,MAX(Data[Updated Month]) = _SelectMonthYear),SUM(Data[Value]),
IF(MAX(Data[Updated Month]) = _SelectMonthYear,SUMX(FILTER(ALL(Data),Data[ID] in _TID),Data[Value]),
BLANK())
)
Result is as below.
Select Aug-21:
Select Sep-21:
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@zhandos , With help from an independent date table having month , try a measure like
measure =
VAR _max = MAXX(allselected('Date1'),'Date1' [Month])
return
calculate(sum(Table[value]) , filter(Table, (Table[Updated Month] = Table[Created Month] && Table[Updated Month] = _max) || Table[Updated Month] = _max))
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |