Moving SUM excluding missing dates in PowerPivot

[gimast]

Hi Power BI Community,

I'm relatively new to DAX so my apologies if I miss an obvious solution here, but I tried different options I found in this forum and couldn't make this work.

So let's say I have this data table:

Date	Location	Category	Value
5/1/21	Paris	A	5
5/1/21	Milan	A	10
5/2/21	Paris	B	10
5/2/21	Milan	B	5
5/4/21	Paris	A	10

What I need is a measure for moving SUM of value (let's say last 3 days). I want this measure to skip missing dates (i.e. 5/3/21) and to work when filtering in a pivot table by one or more of my dimensions (e.g. Location). So for example:

• My measure for 5/1/21 should be 15
• My measure for 5/1/21 when Location==Paris should be 5
• My measure for 5/2/21 should be 30
• My measure for 5/2/21 when Location==Paris and Category==A should be 5
• My measure for 5/4/21 should be 40
• My measure for 5/4/21 when Location==Paris and Category==A should be 15

Any suggestions? 

I tried with time intelligence functions (e.g. DATEINBETWEEN), with TOPN and creating an Index ranking existing dates value but none of these worked as expected. Either it included missing dates or it worked but not when filtering. 
I would prefer a measure but can consider other solutions.

Please note that I'm working on PowerPivot not PowerBI, I found in this forum a viable solution for this but it used the SELECTEDVALUE function which is not available in PowerPivot.

Hope I made this clear.

Thanks for your kind help.

Giacomo

[Jihwan_Kim · Accepted solution]

Hi, @gimast 

Please check the below picture and the sample pbix file's link down below.

I tried to expand the sample a little bit, like below.

All measures are in the sample pbix file, and all steps are numbered in front of each measure.

1 Values Total =

SUM(data[Value])

2 Dates Rank without Blank =

VAR _tablewithvalues =

FILTER (

SUMMARIZE (

ALLSELECTED ( 'calendar' ),

'calendar'[Date],

"@value", [1 Values Total]

),

[@value] > 0

)

RETURN

IF( ISFILTERED('calendar'[Date]),

IF (

NOT ISBLANK ( [1 Values Total] ),

RANKX ( _tablewithvalues, CALCULATE ( MAX ( 'calendar'[Date] ) ),, ASC )

)

)

3 Values Total Latest 3 dates =

VAR _currentrank = [2 Dates Rank without Blank]

VAR _dateswithrank =

FILTER (

SUMMARIZE (

ALLSELECTED ( 'calendar' ),

'calendar'[Date],

"@value", [1 Values Total],

"@rank", [2 Dates Rank without Blank]

),

[@rank] >= _currentrank - 2

&& [@rank] <= _currentrank

)

RETURN

IF ( ISFILTERED ( 'calendar'[Date] ), SUMX ( _dateswithrank, [@value] ) )

https://www.dropbox.com/s/yhbyjkb7b5l6zvm/gimast.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[gimast]

This works perfectly! Thank you, @Jihwan_Kim, for your kind help.
I'm marking this as accepted solution.