how to synchronize two unrelated calendars via slicer value on same page

[wokka]

So i have two calendars in use on the powerbi and I want one calendar ( the Report calendar ) to force another calendar ( the Start calendar ) to its value.

What I wanted to do was somehow read the Report calendar slicer value for the year and pass that automatically to the Start calendar as a filter for a table , so when I change the Report calendar, a second table on the page that uses the Start calendar will be automatically changed behind the scenes to be the same year.

Not sure if we could do this as a custom measure to drag into a text box on the page?

This is a bit complex I think , any help appreciated.    

[pankajnamekar25]

Hello @wokka 

Use a disconnected slicer + DAX filtering logic

 Create a Disconnected Calendar Table (if not already)

Calendar_Report[Date] is used in Slicer 1 (Report visuals)

Calendar_Start[Date] is used in a second visual/table

These are not related in the model.

Create a measure to capture the selected year from the Report calendar

Selected_Report_Year = SELECTEDVALUE(Calendar_Report[Year])

Make sure Calendar_Report[Year] is part of your slicer.

Create a measure to filter visuals using Start calendar based on the selected year

Start_Calendar_Filtered_Measure =

CALCULATE(

    [Some Metric],

    FILTER(

        ALL('Calendar_Start'),

        'Calendar_Start'[Year] = [Selected_Report_Year]

    )

)

Now use Start_Calendar_Filtered_Measure in your visual instead of the raw measure, and it will respect the selection made in Report calendar even though the two calendars are unrelated

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[wokka]

Thank you. How would I adapt your code to pass a year and a month to the new calendar please?

I thought comething like this - maybe two separate custom measures ?

__MeasureYear =

Selected_Report_Year = SELECTEDVALUE(Calendar_Report[Year])

CALCULATE(

    [Some Metric],

    FILTER(

        ALL('Calendar_Start'),

        'Calendar_Start'[Year] = [Selected_Report_Year]

    )

)

__MeasureMonth

Selected_Report_Year = SELECTEDVALUE(Calendar_Report[Month])

CALCULATE(

    [Some Metric],

    FILTER(

        ALL('Calendar_Start'),

        'Calendar_Start'[Month] = [Selected_Report_Month]

    )

)

[v-tejrama]

Hi @wokka,

Thanks for reaching out to the Microsoft fabric community forum.

Your slicers (Year/Month) don’t filter each other because they come from a  disconnected table (Calendar_Report) with no hierarchy or relationships.  

Solution Worked:  
1. Used SELECTEDVALUE( )  to capture slicer selections.  
2. Applied  TREATAS() in a measure to dynamically filter Fact_Sales  via  Calendar_Start:  
  

Here is the DAX :

  
   Sales_Synced_By_Report_Calendar =  
   CALCULATE(  
       SUM(Fact_Sales[Sales]),  
       TREATAS(VALUES(Calendar_Report[Year]), Calendar_Start[Year]),  
       TREATAS(VALUES(Calendar_Report[Month]), Calendar_Start[Month])  
   )  
    

For Slicer Cross-Filtering:  
Switch to a proper date hierarchy (Year → Month → Day) with active relationships to Fact_Sales.  

Please find the attached pbix file for your reference.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Best Regards,
Tejaswi.
Community Support

[wokka]

Hi

It seems to generate the sum value fine, but doesnt seem to work as a filter unfortunately.

What I do is update interractions so the reporting year slicer cant affect the table I want to filter ( otherwise it will alter the  table directly ) and as I alter the slicer, nothing seems to happen to the table visual.

What Ive tried so far as custom dimensions, but these dont appear to work :

__TransferYear =

VAR  repYear = SELECTEDVALUE('Reporting cal'[year],0)

VAR Completion_year =

CALCULATE(  

       SUM('Reporting cal'[year]),  

       TREATAS(VALUES('Reporting cal'[year]),'Start cal'[year] )

        )  

Return

       Completion_year

---------------------------

__TransferYear =

VAR SelectedYear = SELECTEDVALUE('Reporting cal'[year])

RETURN

    CALCULATE(

        COUNTROWS('<some table>'),

        FILTER(ALL('Start cal'[year]),

        'Start cal'[year] = SelectedYear

    ) )

Any help much appreciated..........

[v-tejrama · Accepted solution]

Hi @wokka,

The reason the slicers weren’t filtering the visual is because Calendar_Report  and Calendar_Start aren’t actually related in the data model. Since there’s no direct relationship between them, the slicer selections don’t know how to affect the visual.

To solve this, I used TREATAS(), which basically creates a virtual relationship between the two tables inside the measure itself  no need to build a physical relationship in the model.

Here’s the updated DAX I used:

Sales_Synced_By_Report_Calendar =
CALCULATE(
SUM(Fact_Sales[Sales]),
TREATAS(
VALUES(Calendar_Report[Year]), Calendar_Start[Year]
),
TREATAS(
VALUES(Calendar_Report[Month]), Calendar_Start[Month]
)
)

Now, when I select a Year and Month from the slicers, the measure properly applies those selections to the visual, using Calendar_Start in the background. Even though the tables aren’t linked in the model, it works perfectly with this setup and gives me exactly the result I need.

Thank you,
Tejaswi.

[v-tejrama]

Hi  @wokka ,

Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you,
Tejaswi.

[v-tejrama]

Hi @wokka,

I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.

If the response answered your query, kindly “Accept as Solution” and Give “Kudos” to help others in the community benefit from it as well.

Thank you.

Tejaswi.

[v-tejrama]

Hi @wokka,

I hope the information provided has been useful. Please let me know if you need further clarification or would like to continue the discussion.

If your question has been answered, please “Accept as Solution” and Give “Kudos” so others with similar issues can easily find the resolution.

Thank you.

Tejaswi.