Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

MTD

I'm trying to calculate billable hours for MTD. 

 

I tried the below measure however, it is showing as blank. 

MTD Billable = CALCULATE(SUM(Utilisation[ChargeableHours]), DATESMTD('Work Period'[Date]))
 
To check I put in this measure
MTD Dates Between = CALCULATE(SUM(Utilisation[ChargeableHours]), DATESBETWEEN('Work Period'[Date], DATE(2023,3,1), DATE(2023,3,31)))
 
However, I do not want to update this every month is there a way to automate it.
 
Thanks, 
Karrie 

 

Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    Anonymous Here's a few different ways:

    NC Intenet Sales (MTD) = 
    VAR __Date = MAX('Dates'[Date])
    VAR __YearMonth = YEAR(__Date) * 100 + MONTH(__Date)
    VAR __Table = 
        SUMMARIZE(
            ALL('FactInternetSales'), 
            'FactInternetSales'[OrderDate],
            "__YearMonth", YEAR([OrderDate]) * 100 + MONTH([OrderDate]),
            "__Sales", SUM('FactInternetSales'[SalesAmount]))
    VAR __Result = SUMX(FILTER(__Table, [__YearMonth] = __YearMonth && [OrderDate] <= __Date),[__Sales])
RETURN
    __Result


Internet Sales (MTD) = 
    CALCULATE([Internet Sales],
        FILTER(ALL('Dates'),
            'Dates'[Year Month Number] = MAX('Dates'[Year Month Number]) &&
            'Dates'[Date] <= MAX('Dates'[Date])))



Internet Sales (MTD) 2 = 
    CALCULATE(
        [Internet Sales],
        'Dates'[Year Month Number] = MAX('Dates'[Year Month Number]) &&
        'Dates'[Date] <= MAX('Dates'[Date])
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Greg_Deckler  I'm not sure I did the middle one right

       

      MTD Billable Hours =
      CALCULATE([Chargeable Hours],
      FILTER(ALL('Work Period'[Date]),
      'Work Period'[Date]=MAX('Work Date'[Date]) && 'Work Period'[Date]<= MAX('Work Period'[Date]
      )))
       
      It is still showing blank
      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        3 years ago

        Anonymous So the middle one requires a helper column that combines Year and Month like 202301, 202302, 202303. Basically a calculated column like YEAR('Date'[Date]) * 100 + MONTH('Date'[Date])