CODEWITHSUNDEEP
powerbidax
rubhemaju
rubhemaju

Reputation: 39

power bi dax sum up all entries matching endofmonth filter

Hi I have a data table in powerbi structured

 id     date           data
 1     2022-10-31      123
 1     2022-11-30      456
 1     2022-12-01      789

the data spans multiple user ids and multiple years. This is not the real data

I want to define a measure to sum up all end of month values and only end of month values. If I fed this data, the measure would return 579 (123+456). Also I will use slicers on user ID's in case that changes the DAX

This is the best thing I have so far

Measure = 
SUMX(
    CALCULATETABLE(
        'Table', ENDOFMONTH('Table'[Dates]) 
    ),
    'Table'[data]
    )

Basically create a table2 containing only endofmonth records of main table and sum up the data in table2.

This only returns the last value (as you can see, the values add up every day) so 789

Upvotes: 0

Views: 564

Answers (1)

Jos Woolley
Jos Woolley

Reputation: 9062

I would use simply:

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

Edit: as per your new requirement:

Measure =
VAR T1 =
    ADDCOLUMNS(
        'Table',
        "Last Date in Month",
            CALCULATE(
                MAX( 'Table'[date] ),
                FILTER(
                    'Table',
                    MONTH( 'Table'[date] ) = MONTH( EARLIER( 'Table'[date] ) )
                        && YEAR( 'Table'[date] ) = YEAR( EARLIER( 'Table'[date] ) )
                )
            )
    )
RETURN
    SUMX( T1, IF( 'Table'[date] = [Last Date in Month], 'Table'[data] ) )

Upvotes: 0

Related Questions