Forum Discussion

chema's avatar
chema
New Member
1 year ago
Solved

dax formula

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?

Need Help
  • KNP's avatar
    KNP
    1 year ago

    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's avatar
    Anonymous
    1 year ago

    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.

3 Replies

  • KNP's avatar
    KNP
    Super User
    1 year ago

    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's avatar
    Anonymous
    Not applicable
    1 year ago

    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.