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 am facing some issue with the the above formula as in screenshot below. Also i have attached my Pbix file in the link below for reference.

It would be great if you could review the file and let me know where i am missing

 

 

MTD and YTD pbix  

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

Sorry can't open pbix right now

 

But There seems to be 2 ALL in LASTNONBLANK with Calendar date which is causing this error

 

Also make sure that brackets are closed properly.

 

 

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

    Hi FarhanAhmed,

    Can you email your mail id to send the Pbix?

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