to get the last rolling 12 months ID depending on the selection on date slicer

[jajaAtPowerbi]

would like to get the previous rolling 12 month ID by summarizing calendar table according to the selection of the date slicer

here below are the 2 measures :

end monthID R12M = VALUE(FORMAT(DATE(YEAR(MAX('Calendar'[Date])),MONTH(MAX('Calendar'[Date])), 1),"YYYYMM"))

start monthID R12M = VALUE(FORMAT(DATE(YEAR([end date]),MONTH([end date])-11, 1),"YYYYMM"))

why Calendar_R12Months does not summarize what i expected?

[jajaAtPowerbi]

The data range slicer is selected from 1/12/2022 to 31/1/2023

so i would like to get the monthID counting back from 202301 till 202202

This is the expected result i would like to get but surely not want to hardcode the the value for end_monthID, start_monthID

Can anybody help?

[Anonymous · Accepted solution]

HI @jajaAtPowerbi,

Current power bi does not support to create dynamic calculated column/table based on filter selections. They do not work on the same level and you can't use the child level to affect its parent.

Notice: the data level of power bi(from parent to child level)

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng