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

You need to add a filter statement at the end of your measure to limit dates

 

FILTER(ALL( 'Calendar'[Date]), 'Calendar'[Date]<=MAX( FACT_Monthly_Sales_By_business_Line[Date]))

 

your meausre will be look like this.

 

Billed Hours Last_Year_MTD = CALCULATE ( [Billed Hours], SAMEPERIODLASTYEAR(DATESMTD( 'Calendar'[Date])),FILTER(ALL( 'Calendar'[Date]), 'Calendar'[Date]<=MAX( FACT_Monthly_Sales_By_business_Line[Date]))
)

 

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

    Hi FarhanAhmed,

    The measure did solve my problem to some extent but not completely. If you refere the screenshot below , you can see that though the measure restricts it to maxdate but the total for the previous year still shows the total for the entire month (721961) and not the current date for last year (426089). Screenshot below for reference.

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

      I think the solution suggest by d_gosbell  will work and you may want to do some tweaking by adding HASONEVALUE in the code to not return values for future dates.

       

      You may give it a try as well.

       

      Billed Hours Last Year MTD = 
var lastNonEmtpyDate = LASTNONBLANK(ALL(ALL(Calendar[Date]),[Bill Hours])
return 
IF(HASONEVALUE(ALL(Calendar[Date]),

CALCULATE ( [Bill Hours], SAMEPERIODLASTYEAR(DATESMTD( ALL(Calendar[Date])),FILTER(ALL( ALL(Calendar[Date]), ALL(Calendar[Date]<=MAX( Query1[Date]))
)
,
CALCULATE( [Bill Hours], DATEADD( FILTER(DATESMTD(ALL(Calendar[Date]),ALL(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