Forum Discussion

Powereports's avatar
Powereports
Icon for Helper I rankHelper I
5 years ago
Solved

MTD and YTD calculation

@ Power BI users,   When I try to use SAMEPERIODLASTYEAR to get MTD of previous year, it returns entire months billed hours sum, rather I was looking for only till current year date sum as in scree...
MTD
YTD
  • FarhanAhmed's avatar
    FarhanAhmed
    5 years ago
    Billed Hours Last Year MTD = 
var lastNonEmtpyDate = LASTNONBLANK(ALL(Calendar[Date]),[Billed Hours])
return
IF(HASONEVALUE('Calendar'[Date]),
CALCULATE ( [Billed Hours], SAMEPERIODLASTYEAR(DATESMTD( (Calendar[Date]))),
FILTER( ALL(Calendar[Date]), Calendar[Date]<=MAX( 'Billed Hours MTD dax'[Date]))
),
CALCULATE([Billed Hours], DATEADD( FILTER(DATESMTD((Calendar[Date])),Calendar[Date]<= lastNonEmtpyDate ), -1, YEAR )))
d_gosbell's avatar
d_gosbell
Icon for Super User rankSuper User
5 years ago

If I understand your issue correctly you want the calculation to stop at the last date that has data. So something like the following might do the trick:

Billed Hours Last Year MTD =
var lastNonEmtpyDate = LASTNONBLANK(ALL(Calendar[Date]),[Bill Hours])
return CALCULATE( [Billed Hours], DATEADD( FILTER(DATESMTD(Calendar[Date]), Calendar[Date] <= lastNonEmtpyDate ), -1, YEAR ))