Forum Discussion
Solved
Rolling 12 Month Sum with filter
Hello! I am working on a Rolling 12 month performance which includes actuals up to today (-15) and the budget going forward. Using datesinbetween works great until I only want to display one year (2019).
R12 = Calculate([Amt Actual],
DATESBETWEEN ('DATET'[Date],
NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'DATET'[Date] ) ) ),
( Today()-15)))
+
Calculate([Amt Base],
DATESBETWEEN ('DATET'[Date],
(Today()-15),
LASTDATE ( 'DATET'[Date])))
I am working on using filters, yet PowerBI suggests that I am comparing Date Values with True/False values. Please help me understand why I am getting this error or a better solution if possible. This forumla is for dates between today and the end of the chart which is Dec 2019.
RollingBase =
CALCULATE (
[Amt Base],
FILTER (
ALL ( DATET ),
DATET[Date] <= (MAX ( DATET[Date])
&& DATET[Date] >= (today()-15 ))))
Thank you in advance for your help.
Hi purpletiger ,
It looks like parentheses causes the error. Please try this one.
RollingBase = CALCULATE ( [Amt Base], FILTER ( ALL ( DATET ), DATET[Date] <= MAX ( DATET[Date] ) && DATET[Date] >= ( TODAY () - 15 ) ) )
Best regards,
Yuliana Gu
2 Replies
v-yulgu-msftMicrosoft Employee
Hi purpletiger ,
It looks like parentheses causes the error. Please try this one.
RollingBase = CALCULATE ( [Amt Base], FILTER ( ALL ( DATET ), DATET[Date] <= MAX ( DATET[Date] ) && DATET[Date] >= ( TODAY () - 15 ) ) )
Best regards,
Yuliana Gu
THANK YOU! So simple, I was thrown off by the error message.
