Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
@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!
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
**bleep**, the new VAR doesn't work too...
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
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])
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...
I hope this makes sense and works for your situation.
-Parker
@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!
Glad you figured it out!
It's the standard notation for PT (aka the future World Cup Champions)
@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!
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |