Forum Discussion

Florcita's avatar
Florcita
Regular Visitor
1 year ago
Solved

Problem with a measure to compare values

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

Need Help
Show and Tell
Tips and Tricks
  • audreygerred's avatar
    audreygerred
    1 year ago

    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: 

     

1 Reply

  • audreygerred's avatar
    audreygerred
    Super User
    1 year ago

    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: