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 )))
Powereports's avatar
Powereports
Icon for Helper I rankHelper I
5 years ago

Thanks for the quick correction Farhan. The Dax works but somehow i get no value populated in the visual as below ğŸ˜‘.. Any insights?

FarhanAhmed's avatar
FarhanAhmed
Icon for Community Champion rankCommunity Champion
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 )))
  • Powereports's avatar
    Powereports
    Icon for Helper I rankHelper I
    5 years ago

    Hi FarhanAhmed,

    Thankyou for the Magic!! Saved my day!!