DAX Measure Running Total last 7 values non consecutive dates

Hi,
I would like to calculate the last X ( eg 7) number of units using

I have managed to get some way in this with Power query or DAX using the dates from sales table but I would like to reproduce the measure I have
but to work with the Calendar table ;

I want to Calculate a running total for the last 7 units ; in PQ I used SelectRows ;

Please Login or Register  to view this content.

In DAX I started by summing the dates and then ranking them ;

Sumdate:=SUMX( Table3, Table3[Date])

Then using as them in running total measure ;

Please Login or Register  to view this content.

And then bewtweenn two rankings

Please Login or Register  to view this content.

So is there a way I can get this to work but for the 'calendar' table ? I have attached workbook with PQ, and DAX pivot.

Richard.

Thanks, not had a chance to work through it properly, yesterday I thought about using topn, i also thought about trying
a countrows and allselected,

One think I did find and I've found similar before, is that in m mine using the dates from 'Table', this works where the rank is a VAR

Please Login or Register  to view this content.

)
)


But if you make the RANKX a measure and then use it you get the total,

Please Login or Register  to view this content.

Richard

I did not doubt it would work, but yes, fed tired the first part in studio to get back 7 row table;

I'm not sure about the last ; IF(ISBLANK([TotalUnits]),blank(),CALCULATE([TotalUnits],prev7))

if [total] = blank return blank

re previous response about Measure v VAR ; is the measure outside the current filter context so the total is returned.

Richard.

Thanks, perhaps my new year resolution should be to properly read Mr R & S's Definitive Guide.

RD