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

Question

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.

Can you show some example results of you're currently getting along with the results you're expecting? — Alexis Olson
Here's a sample data from the Credits table -> pastebin.com/zTQvqR2p What I'm trying to get is the average from 28-02-2018 (DD-MM-YYYY format) to 31-03-2018, but I'm getting the average from March only when it should be also adding the last day of the past month to the average. What I'm getting -> 55.018.087 vs. What I should be getting -> 54.738.926 — kotelo

Alexis Olson Alexis Olson · Accepted Answer · 2018-12-27T01:00:09

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

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

1 Answers