topic Re: Sum Difference between different date intervals in DAX Commands and Tips

Sum Difference between different date intervals

AlexIsac16 — Fri, 15 Sep 2023 14:46:08 GMT

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

Re: Sum Difference between different date intervals

Mahesh0016 — Sat, 16 Sep 2023 07:02:58 GMT

AlexIsac16 Please can you elaborate visually?

Re: Sum Difference between different date intervals

Anonymous — Tue, 19 Sep 2023 06:38:51 GMT

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

Re: Sum Difference between different date intervals

sjoerdvn — Tue, 19 Sep 2023 07:59:20 GMT

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))

Re: Sum Difference between different date intervals

AlexIsac16 — Sun, 24 Sep 2023 17:57:20 GMT

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