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.  

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]

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.