CODEWITHSUNDEEP
powerbidax
rubhemaju
rubhemaju

Reputation: 39

power bi dax, sum up all latest monthly entries

Hi I have a data table in powerbi structured

 id     date           data
 1     2022-10-30      123
 1     2022-11-01      130
 1     2022-11-30      456

the data spans multiple user ids and multiple years and it the values are cumulative (like minutes on a phone plan for instance). This is not the real data

I want to add up the end of month data. In the ideal case, my table would be complete and 2022-10-31 would exist for instance, then I could do

Measure = 
CALCULATE(
    SUM( 'Table'[data] ),
    'Table'[dates] = EOMONTH( 'Table'[dates],0 )
)

This returns 456 but I want 579 (123+456). So i cannot use EOMONTH

I think the answer is some combination of the dax above and

    FILTER( Table, Table[date] = MAX( Table[date] ) )

Though if I paste that in solo, it grabs the actual latest date only, not all monthly latest dates

Also I will use slicers on user ID's in case that changes the DAX

Upvotes: 0

Views: 369

Answers (1)

Ozan Sen
Ozan Sen

Reputation: 2615

Please use this measure to get what you need:

Measure_ =
VAR TblSummary =
    ADDCOLUMNS ( YoursTable, "EOM", CALCULATE ( ENDOFMONTH ( YoursTable[date] ) ) )
RETURN
    SUMX ( TblSummary, IF ( [EOM] = [date], [data] ) )

If we test our above measure on a table visual:

Gisla

Upvotes: 1

Related Questions