CODEWITHSUNDEEP
daxpowerpivotmeasure
kotelo
kotelo

Reputation: 13

DAX - Calculate average between last day of past month and last day of current month

I'm working on Excel and Power Pivot and I'm trying to get the average sales between the last day of the past month and the last day of the current month, but I'm unable to get the right expression for it.

I have the "Credits" table and the "Calendar" table, both linked by the "Date" field.

So, this is what I have:

=CALCULATE ( AVERAGE(Credits[Sales] );
FILTER ( Calendar ;
Calendar[DateNum] >= VALUE(FORMAT(STARTOFMONTH(Calendar[Date])-1;"YYYYMMDD"))
&&
Calendar[DateNum] <= VALUE(FORMAT(ENDOFMONTH(Calendar[Date]);"YYYYMMDD"))))

I use that measure in a dynamic table along with the "Month" dimension, but it only shows the average for the full month, not taking into account the filters I'm trying to apply so that it also takes the last day from the previous month.

Upvotes: 1

Views: 534

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40224

I think what's happening is that the month is still in your filter context.

Try FILTER( ALL(Calendar) ; instead.

I think you could probably also simplify your measure a bit. Maybe try something along these lines:

CALCULATE(
    AVERAGE( Credits[Sales] );
    DATESBETWEEN(
        Calendar[Date];
        STARTOFMONTH( Calendar[Date] ) - 1;
        ENDOFMONTH( Calendar[Date] )
    )
)

Upvotes: 0

Related Questions