Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.