How to show last six months data(one row for each month) in MDX query based on selected month filter in Power BI report?

Question

I have created a cube with one fact table and 5 dimension which includes one Date dimension. Hierarchy of date dimension is Year -> Quater -> Month. Here I want to select the last six months data from selected month using mdx query. The output should be Month (Row Level) and Measures on Column level.

Thought of creating a dynamic named set but power BI doesn't consume named sets.

Can anyone please suggest a way to do it in MDX either using a disconnected date dimension or any idea in Power BI?

Answer 1

Take a look at the sample below

select 
[Measures].[Internet Sales Amount]
on 0
,
[Date].[Calendar].[Month].&[2012]&[4].lag(6):
[Date].[Calendar].[Month].&[2012]&[4] 
on 1 
from 
[Adventure Works]

Result