Years slicer includes data from the selected year's previous year.

[Anonymous]

Hi 

I have a dynamic slicer which: 

- displays the last 3 years ( does not include the current year). I use a Date table and use the years for the slicer. So for 2021 year the slicer will display 2020,2019,2018

- filters a fact table that include the orginal and revise data. 

 Sample table below: 

Assesment year	Organisation	Lodged/Revision	Date (report was lodged)	Reported number
2019	Company 1	Lodged	15/ Jan/ 2020	5,000
2019	Company 1	Revision	5/Jan/2021	6,000
2020	Company 1	Lodged	15/Jan/2021	10,000

Annual reporting and revision rules: 

- Companies submit their annual report for an assessment year every 15 January on the  following year. For example, for the 2019 assessment year, they must submit their report by 15 Jan 2020. 

- Revisions on their reported number can be made within 12 months after they lodged their orginal report. 

Relationship: 

Date table[Dates] is connected to the Fact table[Dates] with one to many relationship. 

Problem: 

When I choose an assessment year (For example. 2020) my table also shows revisions from the previous assessment year. (I think its because its filtering using the [Dates] column instead of the [Years] column). Please help. 

My pobix file contains a lot of sensitve data so my apologies if I can't attach it here. 

Thank you.

Jen 

[Anonymous]

Hi Xiaoxin, 

Thanks for your reply. I've already set up an unconnected date table and using it as a slicer. And it is working as intended in terms of displaying the last 3 years (current year not included). What I'm having trouble is when I choose a year, I aside from getting values for 2020 assessment year, I'm also getting values for 2019. 

Below is how I set up the relationships for my slicer, 

The values from the slicer is from the 'Date previous' Table, including the 'Count PY' whic filters the last 3 years. 

Also, just want to note that I'm new to PowerBi so I really appreciate your help. 

Regards

Jen 

[v-shex-msft]

Hi @Anonymous,

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Notice: Please do not attach any sensitive data in the sample.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi Xiaoxin, below are the links to the sample data files. 

Thanks

dummy pbix file 

dummy xlsx file 

[v-shex-msft · Accepted solution]

HI @Anonymous,

You can break the relation from the date table and use year fields as the source of the slicer, then you can write a measure to extract and compare the field value from the slicer and current row context and use it on the table visual 'visual level filter' to filter records.

Mfilter = 
VAR selected =
    MAX ( Dates[Year] )
RETURN
    IF ( MAX ( Table1[Assessment year ] ) = selected, 1, 0 )

Regards,

Xiaoxin Sheng

[Anonymous]

Sorry, I forgot to ask.  The Mfilter work great if you have one tabl/query but how about if you want to filter multiple tables /query with the same year slicer?

This might be an easy fix but I'm new to PowerBI and still learning DAX. Your help will very much appreciated 🙂 

Thank you

Jen

[Anonymous]

 This works. Thank you so much! 🙂 

Jen 

[v-shex-msft]

Hi @Anonymous,

According to your description, it sounds like you want to achieve a custom filter effect.
For this scenario, I'd like to suggest you create an unconnected date table and use it as the source of slicer. Then you can write a measure to extract the selection and compare it with the fact table date to filter records.

Applying a measure filter in Power BI - SQLBI

Regards,

Xiaoxin Sheng