cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eduardomartins
Frequent Visitor

Isfiltered is not working on date hierarchy

Hi guys,

 

I've got a problem here with IsFiltered function. I've already used it several times, but not with dates hierarchy.

For example, I created a measure like this:

 

 

Previous Sum = if(ISFILTERED(Dataset[RefDate].[Year]);
		CALCULATE(SUM(Dataset[Values]);PREVIOUSMONTH(Dataset[RefDate].[Date]));
		CALCULATE(SUM(Dataset[Values]);PREVIOUSYEAR(Dataset[RefDate].[Date])))

 

I use this on a bars graph with drill down on Year/Month. So, when I'm visualizing the year layer, I should have the sum of last year values for each bar. When I drill down to see months, I should have the sum of the last month for each bar. What i'm getting is always the sum of the last month, even on the Year layer.

 

Any help, please?

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@eduardomartins

 

Change the .[Year] to . [Month]

Your condition was always true

 

Previous Sum =
IF (
    ISFILTERED ( Dataset[RefDate].[Month] );
    CALCULATE ( SUM ( Dataset[Values] ); PREVIOUSMONTH ( Dataset[RefDate].[Date] ) );
    CALCULATE ( SUM ( Dataset[Values] ); PREVIOUSYEAR ( Dataset[RefDate].[Date] ) )
)

Good Luck! Smiley Happy

 

EDIT: But I have to say nice Measure for use in Hierarchies! (I will definitely use this) Smiley Happy

View solution in original post

5 REPLIES 5
Sean
Community Champion
Community Champion

@eduardomartins

 

Change the .[Year] to . [Month]

Your condition was always true

 

Previous Sum =
IF (
    ISFILTERED ( Dataset[RefDate].[Month] );
    CALCULATE ( SUM ( Dataset[Values] ); PREVIOUSMONTH ( Dataset[RefDate].[Date] ) );
    CALCULATE ( SUM ( Dataset[Values] ); PREVIOUSYEAR ( Dataset[RefDate].[Date] ) )
)

Good Luck! Smiley Happy

 

EDIT: But I have to say nice Measure for use in Hierarchies! (I will definitely use this) Smiley Happy

View solution in original post

Hi @Sean, thanks for the answer!

 

I've just tried here, but it didn't work 😞

It still brings me the value from "last month" on Years layer. I have something like that:

 

Dataset table: 

 

Date                    Sales

2016-01-01         100.0

2016-02-01         2000.0

2016-03-01         3500.0

2017-01-01         2000.0

 

I created a bars graph and put the date column (hierarchy) on X Axis and selected only Year and Month. On Values I put a Sum of Sales column. Doing that, Power Bi drew a two layers bars graph. I created the measure that I mentioned on previous post that should calculate the amount of previous month, depending on which layer I’m visualizing. That measure i put on tooltip field.

 

My expectations: mouse over the 2017 bar, it would show me the amount of last year (2016). If I'll drill down 2016, the "year" would be filtered, the months bars would be shown and if I mouse over March bar, for example, it would show me the Feb amount.

 

For the months it's ok, I can see the on the tooltip the previous month amount after drill down. Although, on Year layer, it doesn't work. It keeps showing me the last years last month amount. 😞

 

I've tried many things and nothing worked. 😞

@eduardomartins

 

Hi, i use Sean DAX in your dataset Sample and have this result.

 

YearsYearsMonthsMonths




Lima - Peru
Sean
Community Champion
Community Champion

@eduardomartins

 

Okay I think I understand your question now Smiley Happy

 

This should do it - you want same month but previous year right?

 

Previous Sum =
IF (
    ISFILTERED ( Dataset[RefDate].[Month] );
    CALCULATE (
        SUM ( Dataset[Values] );
        SAMEPERIODLASTYEAR ( Dataset[RefDate].[Date] )
    );
    CALCULATE ( SUM ( Dataset[Values] ); PREVIOUSYEAR ( Dataset[RefDate].[Date] ) )
)

Hopefully this is what you want!

Good Luck! Smiley Happy

 

@Sean and @Vvelarde thanks for the answers!!

 

After trying a lot, I had to start over again from ground zero and guess what? It worked!! 😄

What I wanted was exactly what @Sean posted on the his first answer.

 

Thanks guys!

 

 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors