Forum Discussion

gimast's avatar
gimast
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago
Solved

Moving SUM excluding missing dates in PowerPivot

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:

 

DateLocationCategoryValue
5/1/21ParisA5
5/1/21MilanA10
5/2/21ParisB10
5/2/21MilanB5
5/4/21ParisA10

 

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's avatar
    Jihwan_Kim
    5 years ago

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

    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

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

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

    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's avatar
      gimast
      Icon for Microsoft Employee rankMicrosoft Employee
      5 years ago

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