Forum Discussion

AlexIsac16's avatar
AlexIsac16
Regular Visitor
2 years ago
Solved

Sum Difference between different date intervals

Hi everyone,

 

I'm trying to create a measure that calculates the % increase/decrease between the sum of operating profit month to date, and the sum of operating profit during the same time period last month. 

 

 

The first part of the formula is pretty straigthforward using the TotalMTD function. Could anyone tell me how to create a dynamic measure that calculates the sum of profit between this day last month and the start date of last month? 

 

Thank you,

 

Alex

 

 

  • sjoerdvn's avatar
    sjoerdvn
    2 years ago

    You haven't mentioned the table and column names, but it should be something like this.

     

    profit last month td = CALULATE(SUM(fact_table[sales]),DATEADD(DATESMTD('date_table'[date]),-1, MONTH))

     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi  AlexIsac16 ,

    I created some data:

     

    Here are the steps you can follow：

    1. Create measure.

    Measure =
var _today=TODAY()
var _date=DATE(YEAR(_today),MONTH(_today)-1,1)
var _laststart=
MINX(
    FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(_date)&&MONTH('Table'[Date])=MONTH(_date)),[Date])
var _lastcurrent=
DATE(
   YEAR(_today),MONTH(_today)-1,DAY(_today))
return
SUMX(
    FILTER(ALL('Table'),
    'Table'[Date]>=_laststart&&'Table'[Date]<=_lastcurrent),[Value])

    2. Result:

     

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

  • sjoerdvn's avatar
    sjoerdvn
    Solution Sage
    2 years ago

    You haven't mentioned the table and column names, but it should be something like this.

     

    profit last month td = CALULATE(SUM(fact_table[sales]),DATEADD(DATESMTD('date_table'[date]),-1, MONTH))

     

     

    • AlexIsac16's avatar
      AlexIsac16
      Regular Visitor
      2 years ago

      Thank you sjoerdvn, somebody on a different forum suggested the exact same solution and it worked.