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

Hi FarhanAhmed,

I guess this time i tried rectifying and closing the brackets but still unable to get there. Any leads would be appreciated.

FarhanAhmed's avatar
FarhanAhmed
Icon for Community Champion rankCommunity Champion
5 years ago

Seems like first calculate missing 1 close bracket and 2nd one has 1 extra bracket

  • 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!!