Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

@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

Hey @Anonymous

 

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

 

Anonymous
Not applicable

@Anonymous

 

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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

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

Anonymous
Not applicable

@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!

Anonymous
Not applicable

Glad you figured it out!

Anonymous
Not applicable

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

Smiley Very Happy

Anonymous
Not applicable

@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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.