topic Re: to get the last rolling 12 months ID depending on the selection on date slicer in DAX Commands and Tips

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

jajaAtPowerbi — Sun, 13 Aug 2023 17:35:09 GMT

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?

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

jajaAtPowerbi — Mon, 14 Aug 2023 02:04:01 GMT

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?

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

Anonymous — Thu, 17 Aug 2023 01:24:31 GMT

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