Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
orlando9427
Frequent Visitor

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

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!

1 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.

View solution in original post

7 REPLIES 7
v-echaithra
Community Support
Community Support

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
Community Support
Community Support

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
Community Support
Community Support

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
Community Support
Community Support

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

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.

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
Solution Specialist
Solution Specialist

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.