topic Re: Rolling month in Report Server

Rolling month

Priya2007 — Thu, 19 Dec 2019 21:52:35 GMT

Hi ,

I am using below formula to count Calculated_required_dates by 12 month rolling

TTM_sales3 =

VAR Currentdate = EOMONTH(Max ('Date'[CLOSED_DATE]),0)

var PreviousDate = EDATE ( EOMONTH (Currentdate, -1 ), -12 ) +1

VAR Result = CALCULATE (count('Work Delivery'[Calculated_REQUIRED_DATE]), Filter ('Work Delivery',MAX('Work Delivery'[CLOSED_DATE])>=PREVIOUSDATE && MAX('Work Delivery'[CLOSED_DATE])<= CURRENTDATE))

RETURN Result

I am getting the exact result but total is missing.

Re: Rolling month

v-qiuyu-msft — Thu, 26 Dec 2019 05:30:50 GMT

Hi Priya2007,

Assume the [Month_Closed] in the table 'Work Delivery', you can create a measure like below:

MeasureTotal=

VAR __table = SUMMARIZE('Work Delivery',[Month_Closed],"__value",[TTM_sales3])
RETURN
IF(HASONEVALUE('Work Delivery'[Month_Closed]),[TTM_sales3],SUMX(__table,[__value]))

Reference:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Best Regards,
Qiuyun Yu