cancel
Showing results for
Did you mean:
Highlighted
Member

## Rolling 12 month sum for completed months

Hi guys.

I'm trying to calculate a special rolling sum.

I want to calculate a sum from the next month last year and last day current month. I.e., if I select 15 May 2018 the sum will be calculated from 1 June 2017 to 31 May 2018.

I did this:

```MAT U =
VAR mindate = DATE(YEAR(MAX(DimDate[Date]))-1;MONTH(MAX(DimDate[Date]))+1;1)
VAR maxdate = DATE(YEAR(MAX(DimDate[Date]));MONTH(MAX(DimDate[Date]));(MAX(DimDate[Date])))
RETURN
CALCULATE (
[SO UNITS];
FILTER (
(IMS_MOVIMENTOS);
IMS_MOVIMENTOS[DATA] >= mindate && IMS_MOVIMENTOS[DATA] <= maxdate))```

So basically the dates should be filtered always according to the maximum value of the Date table, even if there is a range date selected (even if 14 May 2018 to 15 May 2018 is selected on the date table slicer).

The measure I created is completely off.

What am I missing here?

Cheers!

1 ACCEPTED SOLUTION

Accepted Solutions
Member

## Re: Rolling 12 month sum for completed months

@Anonymous

Thanks for the idea, but I was actually being dumb

This does it. On the CALCULATE, I should filter the CALENDAR table and not the FACT table. Beginner's error but I'm getting back at PBI from a long stop, that's a good excuse.

```MAT U =
VAR mindate =
DATE ( YEAR ( MAX ( DimDate[Date] ) ) - 1; MONTH ( MAX ( DimDate[Date] ) ) + 1; 1 )
VAR maxdate =
ENDOFMONTH ( DimDate[Date] )
RETURN
CALCULATE(SUM(IMS_MOVIMENTOS[UNID]);
FILTER (
ALL( DimDate );
DimDate[Date] >= mindate
&& DimDate[Date] <= maxdate
)

)```

Thanks a lot for your inputs!

9 REPLIES 9
Anonymous
Not applicable

## Re: Rolling 12 month sum for completed months

Hey @svalen

A couple of possible things I see here. I'm not sure that your maxdate variable is calculating the last day of the month. Try this for the maxdate:

`VAR maxdate = DATE(YEAR(MAX(DimDate[Date])),MONTH(MAX(DimDate[Date]))+1,1) - 1`

Also you may need to use

`ALL(IMS_MOVIMENTOS)`

to eliminate the filters on the date for your measure to work properly.

Hope this helps,

Parker

Member

## Re: Rolling 12 month sum for completed months

@Anonymous

I'll try your first suggestion, thanks a lot!

Regarding the second one, if I do that, I'll have the calculation for all the groups (I have 3 foreign keys that I want to group by).

Cheers!

Member

## Re: Rolling 12 month sum for completed months

@Anonymous

**bleep**, the new VAR doesn't work too...

Anonymous
Not applicable

## Re: Rolling 12 month sum for completed months

I think you need to replace the commas with semi-colons. The semi-colons that you had didn't work for me either so I had to switch to commas

Edit: Unless you mean that the VAR is calculating correctly but the entire measure is not. In response to you response about how you cannot use ALL. Maybe you can get around the problem with the 3 groups by creating a variable to grab the current group and then using ALL and explicitly filtering for the current group

Member

## Re: Rolling 12 month sum for completed months

It's the standard notation for PT (aka the future World Cup Champions)

Member

## Re: Rolling 12 month sum for completed months

I honestly don't know how to do that.

I checked the date Vars and they are good now but somehow when they are applied to the calculation it messes up.

```MAT U =
VAR mindate = DATE(YEAR(MAX(DimDate[Date]))-1;MONTH(MAX(DimDate[Date]))+1;1)
VAR maxdate = ENDOFMONTH(DimDate[Date])
RETURN
SUMX (FILTER (
IMS_MOVIMENTOS;
IMS_MOVIMENTOS[DATA] >= mindate
&& IMS_MOVIMENTOS[DATA] <= maxdate);
IMS_MOVIMENTOS[UNID])```
Anonymous
Not applicable

## Re: Rolling 12 month sum for completed months

Try something like:

```MAT U = VAR CurrentGroup = FIRSTNONBLANK(REPLACETHISWITHGROUPFIELD,1)
VAR mindate = DATE(YEAR(MAX(DimDate[Date]))-1;MONTH(MAX(DimDate[Date]))+1;1)
VAR maxdate = ENDOFMONTH(DimDate[Date])
RETURN
SUMX (   FILTER      FILTER (
ALL(IMS_MOVIMENTOS);
IMS_MOVIMENTOS[DATA] >= mindate
&& IMS_MOVIMENTOS[DATA] <= maxdate      );      REPLACETHISWITHGROUPFIELD = CurrentGroup   );
IMS_MOVIMENTOS[UNID])```

Hopefully this works. Replace the values for the group with whatever field you were referrencing before. Walking through this example...

1. Setting the group of the current row as the CurrentGroup
2. Setting the min and max dates for the filters
3. Taking a SUMX
1. Using ALL to eliminate all filters on the table
2. Filtering the table to the desired date range
3. Filtering down further to only show records with the desired Group using the CurrentGroup variable
4. Finally taking the sum of IMS_MOVIMENTOS[UNID] given the current filters in place

I hope this makes sense and works for your situation.

-Parker

Member

## Re: Rolling 12 month sum for completed months

@Anonymous

Thanks for the idea, but I was actually being dumb

This does it. On the CALCULATE, I should filter the CALENDAR table and not the FACT table. Beginner's error but I'm getting back at PBI from a long stop, that's a good excuse.

```MAT U =
VAR mindate =
DATE ( YEAR ( MAX ( DimDate[Date] ) ) - 1; MONTH ( MAX ( DimDate[Date] ) ) + 1; 1 )
VAR maxdate =
ENDOFMONTH ( DimDate[Date] )
RETURN
CALCULATE(SUM(IMS_MOVIMENTOS[UNID]);
FILTER (
ALL( DimDate );
DimDate[Date] >= mindate
&& DimDate[Date] <= maxdate
)

)```

Thanks a lot for your inputs!

Anonymous
Not applicable

Announcements