Forum Discussion

dyee4613v1's avatar
dyee4613v1
Frequent Visitor
1 year ago
Solved

Flexible Month Calculation

Hi.  I'm trying to do MTD/YTD/YTD LY calculations and the calculations need to be flexible based on a month filter.  Here is how I wanted to do it.

 

1. YTD = CALCULATE(Sum(GL[AMOUNT]),'Invoked Function'[YEAR]=2025)

2. YTD LY =CALCULATE(Sum(GL[AMOUNT]),SAMEPERIODLASTYEAR('Invoked Function'[Date]))

 

I think these should be pretty easy.  The challenging part is MTD since I need to filter to the latest month and I'll have multiple months.  Here is what I did.

Month =
Var AllDates = CONCATENATEX(VALUES('Invoked Function'[Month Name]),'Invoked Function'[Month Name],",")
Var Str_Len = Len(AllDates)
Var Result =

Trim(RIGHT(SUBSTITUTE(AllDates, ",",REPT(" ",Str_Len)),Str_Len))
Return
Result
 
This works and returns the latest month.  
 
Based on this, I thought the calculation would be simple

MTD = CALCULATE(SUM(GL[AMOUNT]),FILTER('Invoked Function','Invoked Function'[Month Name] = [Month]))
 
This formula works when I manually type in "June" but when I use the formula it gives me an error.  It says I'm comparing Text with an Integer.  So, I tried modifying it to 

MTD = CALCULATE(SUM(GLPTRN[AMOUNT]),FILTER('Invoked Function','Invoked Function'[Month Name] = FORMAT([Month],"")))

This eliminates the error but doesn't return a value.  I feel like the answer is simple but I can't think of it.
 
 

 

 

Need Help
  • MasonMA's avatar
    MasonMA
    1 year ago

    dyee4613v1 


    Hi, if your Date table is already properly marked as a Date table in Power BI I'd suggest using time intelligence function instead of comparing the date text. 

    So for your MTD it would be 

    MTD =
    VAR MaxSelectedDate = MAX('Invoked Function'[Date])
    RETURN
    CALCULATE(
    SUM(GL[AMOUNT]),
    DATESMTD('Invoked Function'[Date]),
    'Invoked Function'[Date] <= MaxSelectedDate
    )

3 Replies

  • MasonMA's avatar
    MasonMA
    Super User
    1 year ago

    dyee4613v1 


    Hi, if your Date table is already properly marked as a Date table in Power BI I'd suggest using time intelligence function instead of comparing the date text. 

    So for your MTD it would be 

    MTD =
    VAR MaxSelectedDate = MAX('Invoked Function'[Date])
    RETURN
    CALCULATE(
    SUM(GL[AMOUNT]),
    DATESMTD('Invoked Function'[Date]),
    'Invoked Function'[Date] <= MaxSelectedDate
    )

  • dyee4613v1's avatar
    dyee4613v1
    Frequent Visitor
    1 year ago

    I'm an idiot.  I was trying calculate table and all this nonsense.  The normal MTD calculation does everything I need.  Thank you Mason!