dax formula

[chema]

HI everyone,

I have cumulative data that includes common columns for month, year, and region, covering investment data from 2021 to 2024. Since this data is cumulative, I need to extract the value for the last month of each year for each region. Despite trying several DAX formulas, I have not been able to achieve the desired result. Could you suggest a solution?

[KNP · Accepted solution]

As long as you have a date table in your model you could use either CLOSINGBALANCEMONTH or CLOSINGBALANCEYEAR depending on what you're looking for.

[Anonymous · Accepted solution]

Hi @chema ,

Thanks for KNP's reply!
And @chema , I created the sample data myself based on your description:

Then you can use this DAX to create a measure:

LastMonthValue = 
CALCULATE(
    MAX('Investments'[Value]),
    FILTER(
        'Investments',
        'Investments'[Month_number] = CALCULATE(
            MAX('Investments'[Month_number]),
            ALLEXCEPT('Investments', 'Investments'[Year], 'Investments'[Region])
        )
    )
)

And put the measure into the table visual, the final output is as below:

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

[KNP · Accepted solution]

As long as you have a date table in your model you could use either CLOSINGBALANCEMONTH or CLOSINGBALANCEYEAR depending on what you're looking for.

[chema]