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 )))
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
5 years ago

You are nearly there. In addition to SAMEPERIODLASTYEAR you also need a filter from your dates table that includes all dates that are older than or equal to "Today minus one year". Depending on your dataset refresh frequency the easiest way to implement that is as a calculated column that gets updated during refresh.