Filters: "In this year" unless january?

[Irwin]

Hi Guys,

I have some KPI's on my dashboard. When the year turns all my figures turns blank - obviously since I have told PBI to filter "in this year". However, management still needs to see last year because december reporting is not done before mid january.

How can I create a filter that automatically switches from reporting "In this year" to "In the last calendar year" ? Or maybe just doesnt update "in this year" untill february?

[v-rzhou-msft · Accepted solution]

Hi @Irwin 

According to your statement, I know that you want to see last calendar year if your month is January, you want to see this year untill february.

Here I suggest you to create a filter measure to filter your visual.

Filter Measure = 
VAR _TODAY =
    TODAY ()
VAR _TODAY_YEAR =
    YEAR ( _TODAY )
VAR _TODAY_MONTH =
    MONTH ( _TODAY )
RETURN
    IF (
        _TODAY_MONTH = 1,
        IF ( MAX ( 'Date'[Year] ) = _TODAY_YEAR - 1, 1, 0 ),
        IF ( MAX ( 'Date'[Year] ) = _TODAY_YEAR, 1, 0 )
    )

Add this measure into visual level filter field in this visual and set it to show items when the value =1.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Irwin]

Hi Rico,

Thanks alot for this suggestion. I think it might be the solution, I just have a hard time understanding exactly how this measure filters my data - and I cannot really see if it shows "this year" data before february.

From the way I read the measure:

it checks if its january.

- If yes, then it checks if the current year (max year?) is equal to todays year minus 1. It will always be that in january?

- If no, then it check if the current year (max year?) is equal to todays year. 

How does PBI interpret the measure to do the filtering. Im confused 🙂

[v-rzhou-msft]

Hi @Irwin ,

Power BI only support us to add measure into visual level filter field. So here we add the measure into the visual level filter and set it to show items when the value =1. Then we could only see the results in which measure will return 1.

You can download my sample and learn more details.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Irwin]

Hi Rico,

Thank you for your help. I will use your filter and look even more into your sample. I lookforward to february to see how it will work there 🙂

[amitchandak]

@Irwin , Usually we create a column like this month and last month and save on that, so keep on reflecting data till last month

Month Type = Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY")
)

or

Month Type = Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1) && day(Today())<= 15 ,"Current Month" ,

eomonth([Date],0) = eomonth(Today(),0) && day(Today())> 15 ,"Current Month" ,
Format([Date],"MMM-YYYY")
)

save on last month or Current Month

Default Date Today/ This Month / This Year: https://www.youtube.com/watch?v=hfn05preQYA

[Irwin]

Hi,
Thank you for your answer.
Do you mean that I should create a new coloum inside my date table with one of those measures?

What do you mean when you write "Usually we create a column like this month and last month and save on that, so keep on reflecting data till last month"
- Save on that ? Do you mean I should add the new "month type" to my filters? 

I would like to result to be that in january 2022 PBI still displayes data from all the months of 2021. Then on february 2022 PBI removes all data from 2021 and displayes only data from january and february 2022. Reporting is "a month behind" the actual date.

Again thank you very much for your help 🙂

[amitchandak]

@Irwin ,  We can not write a function to initialize, so we have to create a column, using today.

The column I suggested has all months and this month/last month or current month.

You need to save those values in the slicer. refer to suggested video

[Irwin]

Hi,
Ah, I see what you mean, however, my dash board is "non interactive" - there is no slicer. Higher ups dont need to slice the data. They just need to quickly see KPI's for last month.

One of the figures is as below. Here I have changed "In this year" filter to "In the last calendar year".