Aggregate values from previous month if there was a record update.

[zhandos]

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?

[v-rzhou-msft · Accepted 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.

[zhandos]

Hi @v-rzhou-msft  , looks fantastic. The only question from my side is how to show total?

[amitchandak]

@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))