Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Experts
See sample file.
https://www.dropbox.com/s/erp5exx8bbhi57v/Rolling%20%281%29.pbix?dl=0
I want to amend my meaure in my table (see below) so my start date is 9 months after the first date in the table i.e First Date is (dec-18) - hence start date should be (Aug-19). All previous values to be shown as blank(zero).
Solved! Go to Solution.
Hi @Anonymous ,
Similar issue here: https://community.powerbi.com/t5/Desktop/Tricky-Rolling-3-and-6-Months/td-p/1942330
Based on my test, you need to delete the relationship between two tables firstly.
And then please try this measure:
3 mtn Rolling =
VAR _LastDate =
LASTDATE ( 'RollingData'[Month] )
VAR _0 =
CALCULATE (
SUM ( RollingData[Month 0] ),
FILTER (
ALL ( 'RollingData' ),
[Month] <= _LastDate
&& [Month] > DATEADD ( _LastDate, -3, MONTH )
)
)
VAR _1 =
CALCULATE (
SUM ( RollingData[Month 1] ),
FILTER (
ALL ( 'RollingData' ),
[Month] <= DATEADD ( _LastDate, -1, MONTH )
&& [Month] > DATEADD ( _LastDate, -4, MONTH )
)
)
VAR _2 =
CALCULATE (
SUM ( RollingData[Month 2] ),
FILTER (
ALL ( 'RollingData' ),
[Month] <= DATEADD ( _LastDate, -2, MONTH )
&& [Month] > DATEADD ( _LastDate, -5, MONTH )
)
)
VAR _3 =
CALCULATE (
SUM ( RollingData[Month 3] ),
FILTER (
ALL ( 'RollingData' ),
[Month] <= DATEADD ( _LastDate, -3, MONTH )
&& [Month] > DATEADD ( _LastDate, -6, MONTH )
)
)
VAR _4 =
CALCULATE (
SUM ( RollingData[Month 4] ),
FILTER (
ALL ( 'RollingData' ),
[Month] <= DATEADD ( _LastDate, -4, MONTH )
&& [Month] > DATEADD ( _LastDate, -7, MONTH )
)
)
VAR _5 =
CALCULATE (
SUM ( RollingData[Month 5] ),
FILTER (
ALL ( 'RollingData' ),
[Month] <= DATEADD ( _LastDate, -5, MONTH )
&& [Month] > DATEADD ( _LastDate, -8, MONTH )
)
)
VAR _6 =
CALCULATE (
SUM ( RollingData[Month 6] ),
FILTER (
ALL ( 'RollingData' ),
[Month] <= DATEADD ( _LastDate, -6, MONTH )
&& [Month] > DATEADD ( _LastDate, -9, MONTH )
)
)
VAR _SUM = _0 + _1 + _2 + _3 + _4 + _5 + _6
RETURN
IF(DATEDIFF(MAX('Calendar'[Date]), MAX('RollingData'[Month]),MONTH)>=8,_SUM,BLANK())
The final output is shown below:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Similar issue here: https://community.powerbi.com/t5/Desktop/Tricky-Rolling-3-and-6-Months/td-p/1942330
Based on my test, you need to delete the relationship between two tables firstly.
And then please try this measure:
3 mtn Rolling =
VAR _LastDate =
LASTDATE ( 'RollingData'[Month] )
VAR _0 =
CALCULATE (
SUM ( RollingData[Month 0] ),
FILTER (
ALL ( 'RollingData' ),
[Month] <= _LastDate
&& [Month] > DATEADD ( _LastDate, -3, MONTH )
)
)
VAR _1 =
CALCULATE (
SUM ( RollingData[Month 1] ),
FILTER (
ALL ( 'RollingData' ),
[Month] <= DATEADD ( _LastDate, -1, MONTH )
&& [Month] > DATEADD ( _LastDate, -4, MONTH )
)
)
VAR _2 =
CALCULATE (
SUM ( RollingData[Month 2] ),
FILTER (
ALL ( 'RollingData' ),
[Month] <= DATEADD ( _LastDate, -2, MONTH )
&& [Month] > DATEADD ( _LastDate, -5, MONTH )
)
)
VAR _3 =
CALCULATE (
SUM ( RollingData[Month 3] ),
FILTER (
ALL ( 'RollingData' ),
[Month] <= DATEADD ( _LastDate, -3, MONTH )
&& [Month] > DATEADD ( _LastDate, -6, MONTH )
)
)
VAR _4 =
CALCULATE (
SUM ( RollingData[Month 4] ),
FILTER (
ALL ( 'RollingData' ),
[Month] <= DATEADD ( _LastDate, -4, MONTH )
&& [Month] > DATEADD ( _LastDate, -7, MONTH )
)
)
VAR _5 =
CALCULATE (
SUM ( RollingData[Month 5] ),
FILTER (
ALL ( 'RollingData' ),
[Month] <= DATEADD ( _LastDate, -5, MONTH )
&& [Month] > DATEADD ( _LastDate, -8, MONTH )
)
)
VAR _6 =
CALCULATE (
SUM ( RollingData[Month 6] ),
FILTER (
ALL ( 'RollingData' ),
[Month] <= DATEADD ( _LastDate, -6, MONTH )
&& [Month] > DATEADD ( _LastDate, -9, MONTH )
)
)
VAR _SUM = _0 + _1 + _2 + _3 + _4 + _5 + _6
RETURN
IF(DATEDIFF(MAX('Calendar'[Date]), MAX('RollingData'[Month]),MONTH)>=8,_SUM,BLANK())
The final output is shown below:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |