Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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!
EDIT: But I have to say nice Measure for use in Hierarchies! (I will definitely use this)
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!
EDIT: But I have to say nice Measure for use in Hierarchies! (I will definitely use this)
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. 😞
Okay I think I understand your question now
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!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |