Problem with a measure to compare values

[Florcita]

Hi guys! How are you? I need some help!

I dont know how to calculate a DAX measure. I tried several ways and failed to do it.

On one hand, i have a measure that cumulatively summarizes "operation fee" (and indicator that calculates the accumulated income for the current month) with this DAX:

Fee Mensual = CALCULATE(sum(TradeOrder[operationFee]))

And, on the other hand, i need to calculate that accumulated "operation fee" for the same date of the previous month. Example: if i do it today, i compare the accumulated income for October with the accumulated income until September the 8th.

The measure i used to calculate the second indicator es:

AcumuladoMovilMesAnterior = CALCULATE(SUM(TradeOrder[operationFee]), FILTER(Dim_Calendario,

Dim_Calendario[Date] <= EOMONTH(MAX(Dim_Calendario[Date]), -1)
&& DAY(Dim_Calendario[Date]) <= DAY(MAX(Dim_Calendario[Date])) ) )

But for some reason, it doesnt return the correct value.

Hope you can help me with this!

Thanks

[audreygerred · Accepted solution]

Hi! You can use the below DAX:

AmountLastMonthToDate =

VAR TodayDate = TODAY()

VAR StartOfCurrentMonth = DATE(YEAR(TodayDate), MONTH(TodayDate), 1)

VAR EndOfCurrentMonth = TodayDate

VAR StartOfLastMonth = EOMONTH(TodayDate, -2) + 1

VAR EndOfLastMonth = EOMONTH(TodayDate, -1)

VAR LastMonthToDate = DATESBETWEEN(

    'YourDateTable'[Date],

    StartOfLastMonth,

    StartOfLastMonth + DAY(TodayDate) - 1

)

RETURN

CALCULATE(

    [Operation Fee,

    LastMonthToDate

)

I created the below sample data:

Then, I added the date to filters on this page (you could also use all pages) and set it to relative date for in this month: