cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
svalen Member
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
svalen Member
Member

Re: Rolling 12 month sum for completed months

@Anonymous

 

Thanks for the idea, but I was actually being dumb Smiley Very Happy

 

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!

View solution in original post

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

 

svalen Member
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!

svalen Member
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

svalen Member
Member

Re: Rolling 12 month sum for completed months

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

Smiley Very Happy

svalen Member
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

svalen Member
Member

Re: Rolling 12 month sum for completed months

@Anonymous

 

Thanks for the idea, but I was actually being dumb Smiley Very Happy

 

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!

View solution in original post

Anonymous
Not applicable

Re: Rolling 12 month sum for completed months

Glad you figured it out!

Helpful resources

Announcements
October 2019 Community Highlights

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.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

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

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 160 members 1,722 guests
Please welcome our newest community members: