Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |