cancel
Showing results for
Did you mean:
Highlighted
lcbdax Frequent Visitor

## Rolling SUM with Datesbetween where Initial Date is blank - Error

Hello,

I have one measure which is the following:

`Margin = DIVIDE(CALCULATE([Margin],KEEPFILTERS('Program'[Model]="Y")),[Margin PM],BLANK())`

And I made two rolling sum measures, one using a SUMX and the other SUM.

```RollingMargin(SUM) = var between =
DATESBETWEEN(
'Date'[Date],
LASTDATE (PREVIOUSMONTH('Date'[Date])))

return
CALCULATE([Margin],between)```
```RollingMargin(SUMX) =
var between =
DATESBETWEEN(
'Date'[Date],
LASTDATE (PREVIOUSMONTH('Date'[Date])))
return
CALCULATE(SUMX(VALUES('Date'[MonthID]),[Margin]),between)```

As displayed in the image below, given there is no data in January, using the SUM with datesbetween yields only null values, I assume because the range includes a non-existing Date as 1st parameter, or maybe cause of the base measure is using DIVIDE (which I changed to alternate result as 0 and got the same result as below, but instead of blank, zeros).

The 2nd Table is using a 1 Month interval.
If I would use a 12 Month interval then for SUM everything would be blank
Is there a way to avoid this? I would like to use SUM as it yields results faster. Thank you!

5 REPLIES 5
amitchandak Super Contributor

## Re: Rolling SUM with Datesbetween where Initial Date is blank - Error

In case of non-continuous date prefer calculation like the one given below

```Var _last_year= (max('Date'[Date Filer]))-365
Var   _This_year=year(max('Date'[Date Filer]))
Var   _min_This_year=year(max(STARTOFYEAR('Date'[Date Filer])))

Var _last_year_val= CALCULATE(sum(Sales[Sales Amount]),(Sales[Sales Date])<=_last_year && (Sales[Sales Date]) >=_min_last_year)
Var   _This_year_val =CALCULATE(sum(Sales[Sales Amount]),(Sales[Sales Date])<=_This_year && (Sales[Sales Date]) >=_min_This_year)
return
(_This_year_val-_last_year_val)/_last_year_val*100```

lcbdax Frequent Visitor

## Re: Rolling SUM with Datesbetween where Initial Date is blank - Error

My Margin formula its a SUM of a column, did I mistake up top.

Margin = DIVIDE(CALCULATE(SUM(Table[Values]),KEEPFILTERS('Program'[Model]="Y")),[Margin PM],BLANK())

v-lid-msft Super Contributor

## Re: Rolling SUM with Datesbetween where Initial Date is blank - Error

Hi @lcbdax ,

To use Time intelligence function, Continuous time is necessary for most time. you can try to generate a calendar table and using in your DAX.

`Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))` The two formulas seems have same result on my side, Could you please provide more details about it? BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-lid-msft Super Contributor

## Re: Rolling SUM with Datesbetween where Initial Date is blank - Error

Hi @lcbdax ,

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

luiscb Member

## Re: Rolling SUM with Datesbetween where Initial Date is blank - Error

Hi again,

Sorry for the late response. You used a static value for Margin PM.
Use this formula instead and you will get the same issue I am talking about:

`Margin PM = CALCULATE(SUM('Table'[Values]),PREVIOUSMONTH('Date'[Date]))`
I think this is only possible via Sumx.
Thanks!

Announcements #### October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.  #### Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future. #### Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI. Top Kudoed Authors
Users Online
Currently online: 436 members 3,724 guests
Recent signins:
• jailenwong • KelliKnitsAlot • souzacaleb • clynlind • smitasumant1 • Sweet-T • jitendrapandey • Afonso • GOTG • manikanta • mxs2135 • anburaj1985 • aeliseev88 • wilso4m9 