Graph six-month-rollback data deleting Date Hierarchy on main calendar table

[orlando9427]

Hi,

I'm creating a dashboard where it is necessary to graph the six months prior to a selected month in a slicer. Following this tutorial Show previous 6 months of data from single slicer selection - SQLBI, I have obtained the required visual, unfortunately, creating an inactive relationship between 'Date' and 'Previous Date' removes the Date Hierarchy in the 'Date' table, which is necessary for other visuals in the dashboard. Is there any other way to graph the six months prior without removing the Date Hierarchy from this table?

Thank you in advance!

[v-echaithra]

Hi @orlando9427 ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra]

Hi @orlando9427 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra]

Hi @orlando9427 ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra]

Hi @orlando9427 ,

You can try adding a calculated column that flags the last six months, use this column as a filter in your visuals.

IsLast6Months =
VAR SelectedMonth = MAX('Date'[Month])
VAR SelectedYear = MAX('Date'[Year])
RETURN
IF(
'Date'[Year] * 12 + 'Date'[Month] >= SelectedYear * 12 + SelectedMonth - 6 &&
'Date'[Year] * 12 + 'Date'[Month] <= SelectedYear * 12 + SelectedMonth,
"Last 6 Months",
"Older"
)

PLease do refer thie below document which is similar to your issue.
https://community.fabric.microsoft.com/t5/Desktop/Show-previous-6-months-dynamic/m-p/75013 

If this post helps, please give us Kudos and consider marking it Accept as solution to assist other members in finding it more easily.

Regards,
Chaithra

[orlando9427 · Accepted solution]

Thank you for your response, but it is required that the month selected in the slicer be dynamic. What I have currently done is link the second calendar table directly to the tables.

[v-echaithra]

Hi @orlando9427 ,

Thank you for sharing your update and confirming that you dont have any issue. i request you to please accept your own post as the solution, this will help other community members who might face a similar issue.

Thanks again for your contribution!

[maruthisp]

Hi @orlando9427 ,

Can yo try the below DAX expression:

Sales Rolling 6 Months =
VAR SelectedMonthStart =
// If your slicer is selecting on the first of each month,
// this will be that date. Otherwise adjust to FIRSTDATE or similar.
SELECTEDVALUE( Calendar[Date] )

// Calculate the first day of the 6-months-ago window:
VAR WindowStart =
EDATE( SelectedMonthStart, -5 )
// e.g. if you picked 2025-06-01 this returns 2025-01-01

// Calculate the last day of your selected month:
VAR WindowEnd =
EOMONTH( SelectedMonthStart, 0 )
// e.g. 2025-06-30

RETURN
CALCULATE(
[Total Sales],
// keep only dates between Start and End inclusive
Calendar[Date] >= WindowStart,
Calendar[Date] <= WindowEnd
)

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X