Last 12 months Std Margin calculation

[tirumalasettiv]

Hello ,

I need to create past 12 months standard margin per each entity,

but I am not sure what to write in the filter condition. Could someone guide me?

For example, entity AE0013, 09/01/2021 last 12 months Std Margin should be the standard margin from 10/01/2020 to 09/01/2021

I have tried the below code with DATESINPERIOD, but got the error.

[Anonymous]

what's your meaning of get last 12 months margin?  sum margin of all the last 12 months ? 

Last12 = CALCULATE(SUM('Table'[Std Margin%]),FILTER('Table',[Date]>EOMONTH(EARLIER([Date]),-12)&&[Date]<=EARLIER([Date])))

[v-xiaosun-msft · Accepted solution]

Hi @tirumalasettiv ,

According to your description, here is my solution.

I created a sample.

Create a DATE table.

DATE =
CALENDAR (
    DATE ( 2021, 1, 1 ),
    DATE ( 2022, 12, 1 )
)

Create a column.

Column =
CALCULATE (
    SUM ( HFM_IC_MAIN[Std Margin%] ),
    DATESINPERIOD (
        'DATE'[Date],
        'HFM_IC_MAIN'[Date],
        -1,
        YEAR
    )
)

Final output:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

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

[Jihwan_Kim]

Hi,

If dim-calendar-table is not there, please try using EOMONTH function with FILTER function.

somthing like,

FILTER ( ..... , HFM_IC_MAIN[date] > EOMONTH( TODAY(), -13) )

https://dax.guide/eomonth/

[tirumalasettiv]

No luck , geeting last 12 months Std Margin % same as Std Margin.