Current month value calculated for the current year and last years

[c0rp]

Hello there,

im trying to calculate the Value of a KPI for the current Month (Sep. 2022) and dynamicly for the same months of the previous years. this behavoir should be in the Year hierachy only, else it should just represent the value of the month. (so on the month Hierachy the Values stay like the Image)

Result should look like this:

Measure Last Month	2021	2022
EBIT	16	21
Margin	13	45
Staffloss	75	79

https://drive.google.com/file/d/1Pm-aVU1yzisKmxRfC9BF1nVGARjbgdXI/view?usp=sharing 

So Far i didnt manage to get the Results with sameperiodlast year... 
Thank you for the Help!

[c0rp]

i tried your solution, its not working for me. could you please implement it in the sample file?

var lastdate_ = CALCULATE (
    MAX ( 'Fact'[Date] ),
    FILTER (
        ALL(Fact'),
        [KPI] = MAX( 'Fact'[KPI])
            && NOT('Fact'[KPI]=0)
    )
)

this Measure returns the last Valuedate, i need this date for the previous Year.
but Sameperiodlastyear or Previuosyear are not wokring for me.

[amitchandak]

@c0rp ,with SAMEPERIODLASTYEAR using a date table marked as date table you can get last year data. But you can not get last year date and column, you will only get that under this year's column under a new measure, like the second table (but a new measure)

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

for that display, you should consider the calculation group

Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0