Show active Years (years of fact table) in my slicer

[marakud971]

Hi everybody,

In my Year slicer,
I have all the years from my calendar table.
However, I only want to display the available years from my fact table.

To achieve this,
I used the date from my fact table in my slicer by creating a calculated dimension Year and Month, and it works well.

However, this modification has caused an error in my calculated measure Sales Year-1 which was fetching my Date field from my Calendar table.

SALES Y-1 = CALCULATE( [SALES], SAMEPERIODLASTYEAR(T_DIM_CALENDRIER[Date]))

I know that I need a link between my slicer and my calculated measure. I have tried different manipulations, but I can't use my measure anymore if I keep my modification on my slicer.

Even when using the date in my fact table in my calculated measure, it doesn't work. Or even by adding the double filtering direction in the join. Can you please help me?

Thanks a lot 

[amitchandak]

@marakud971 , You measure seems fine. But if you want to use inactive join, make sure you use a measure which already have userelationship in TI measue

or try like

example

calculate( calculate( SUM(Sales[Sales Amount]),USERELATIONSHIP ('Sales'[CreateDate], 'Date'[Date])),DATESMTD('Date'[Date]))

MakeWhy Time Intelligence Fails - Powerbi 6 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...

[marakud971]

Hi @amitchandak ,

In my case, i use the active relation in my measure.

Here the error message 

When i use, in my slicer,  the date field of my Calendar Table , it's okey.
But when i use, in my slicer, the calculated YEAR and Month colunm, it's KO.

[amitchandak · Accepted solution]

@marakud971 , Slicer on any period, Period on Visual and date in Measure all should come from T_DIM_Calendar and T_DIM_Calendar should be marked as date table and should have all the dates.

In such a case, it should work with a single directional join.

Do not use date in TI function from you fact/table