Forum Discussion

UncleLewis's avatar
UncleLewis
Icon for Responsive Resident rankResponsive Resident
4 years ago
Solved

Rolling Sum Skip Blanks

Hi all,

 

Is it possible to skip blanks in this rolling sum formula below so that is a date is missing in the fact table, DAX goes to the next available date?

I was checking against a Pivot in Excel and I noticed there is no data for 1/13, 1/14, 1/15.
So in Excel takes 1/12 as the 11th day and 1/16 as the 12th day.
But that is not what is happening in the DAX formula so their is a small variance.

The sample pbix file is here: Rolling28_PBIX 


Rolling_28 =
CALCULATE([Total Value],
DATESINPERIOD(Data[Date],
LASTDATE(Data[Date]),-27,DAY
)
)

Thanks,
-w
Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

    Thank you for providing the sample data. That helps a lot with proposing a potential solution.

    R28_skip = 
var d = SELECTEDVALUE(Data[Date])
var T28= CALCULATETABLE(TOPN(28,Data,Data[Date],DESC),Data[Date]<=d)
return sumx(T28,[Amount])

     

     

    see attached

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    Thank you for providing the sample data. That helps a lot with proposing a potential solution.

    R28_skip = 
var d = SELECTEDVALUE(Data[Date])
var T28= CALCULATETABLE(TOPN(28,Data,Data[Date],DESC),Data[Date]<=d)
return sumx(T28,[Amount])

     

     

    see attached

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    Please show the expected result.  For clarification beyond doubt, please also take 2 months in your dataset and show the epected result for both months.