Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

MTD Billable Hours

I'm trying to calculate MTD Billable Hours and I was wondering if the below measure to calculate Last Month Previous Billable hours could be formated to work 

 

Last Month Billable Hours =
VAR PreviousMonths=1
VAR SelYear = If(MONTH(TODAY())-PreviousMonths<=0, YEAR(TODAY())-1, YEAR(TODAY()))
VAR SelMonth= IF(MONTH(TODAY())-PreviousMonths<=0, MONTH(TODAY())-PreviousMonths +12, MONTH(TODAY())-PreviousMonths)
VAR SelDay= DAY(TODAY())

VAR SelMonthDay = FORMAT(DATE(SelYear,SelMonth,SelDay), "MMMM YYYY")

Return
CALCULATE([Chargeable Hours], FILTER('Work Date', 'Work Date'[Calendar Month Of Year]=SelMonthDay))

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Anonymous ,

     

    MTD= calculate(Sum('order'[Qty]),DATESMTD('Date'[Date]))

     

    LMTD =CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)))

     

    You may to full if full month is in scope

     

    LMTD QTY forced=
    var _max = date(year(today()),month(today())-1,day(today()))
    return
    if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
    //OR
    //CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
    //TOTALMTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)


    LMTD QTY forced=
    Var _Maxdate = MAX('Date Table'[Date])
    var _max = date(year(_Maxdate),month(_Maxdate)-1,day(_Maxdate))
    return
    if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
    //OR
    //CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
    //TOTALMTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)