Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
what's your meaning of get last 12 months margin? sum margin of all the last 12 months ?
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.
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) )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
No luck , geeting last 12 months Std Margin % same as Std Margin.
User | Count |
---|---|
54 | |
23 | |
19 | |
18 | |
17 |
User | Count |
---|---|
89 | |
87 | |
50 | |
34 | |
22 |