Forum Discussion
Normalising a Time Series with Date Hierarchy
Hi Anonymous
I think a visual calculation would work quite well here, since it calculates correctly regardless of the field on the chart's axis.
The traditional measure-based approach would need to detect the granularity of the axis, which is possible but a little tedious.
For visual calculations, you could create a visual calculation like this (assuming Revenue_Sum is the underlying measure):
Revenue Normalised = VAR MinRevenue = MINX ( ROWS, [Revenue_Sum] ) VAR MaxRevenue = MAXX ( ROWS, [Revenue_Sum] ) VAR CurrentRevenue = [Revenue_Sum] VAR Result = DIVIDE ( CurrentRevenue - MinRevenue , MaxRevenue - MinRevenue ) RETURN ResultSmall example PBIX attached.
Would you be happy with this approach or would you like an example of a traditional measure-based approach?
Regards
Super UserHi Anonymous
I think a visual calculation would work quite well here, since it calculates correctly regardless of the field on the chart's axis.
The traditional measure-based approach would need to detect the granularity of the axis, which is possible but a little tedious.
For visual calculations, you could create a visual calculation like this (assuming Revenue_Sum is the underlying measure):
Revenue Normalised =
VAR MinRevenue = MINX ( ROWS, [Revenue_Sum] )
VAR MaxRevenue = MAXX ( ROWS, [Revenue_Sum] )
VAR CurrentRevenue = [Revenue_Sum]
VAR Result = DIVIDE ( CurrentRevenue - MinRevenue , MaxRevenue - MinRevenue )
RETURN
Result
Small example PBIX attached.
Would you be happy with this approach or would you like an example of a traditional measure-based approach?
Regards
Hi Owen,
This is exactly it! Thank you so much!
I had absolutely no idea that visual calculations were even a thing. I had to look up on how to activate it for my PBI desktop. I stepped down in the date filter and it still works.
I notice the use of the keyword 'ROWS', this is my first time seeing this. So the way the code works is that it is looking at the visual's table's rows, rather than the underlying data. Then if we update the table, i.e change filter levels, the visual's tables will update (increase or reduce # of table rows) and the calculation will reapply itself again. Then the rest of the normalisation is as per normal. That's incredibly handy.
For my own learning, what would the traditional measure based approach look like? The summarising of the rows is where i got tangled up. I'd imagine it would address this!
Thank you again!
Regards,
Nam
- OwenAuger
Glad to have helped 🙂
I should mention that visual calcs are still in preview, so there could be some changes coming, but I wouldn't expect the overall functionality to change.
As an alternative, I have added an example using a measure that you could write to achieve the same thing.
The measure needs have to cater for each possible column that could be used on the axis, so it ends up a bit long-winded. This is partly because DAX doesn't allow conditional table expressions, only conditional scalar expressions.
A measure like this needs to ensure that lower levels are tested for before higher levels of the date hierarchy, since ISINSCOPE ( <column> ) will be true for any column that is visible on the axis:
Revenue Normalised Measure = // Year = 1, Quarter = 2, Month = 3, Date = 4 VAR AxisLevel = SWITCH ( TRUE (), ISINSCOPE ( 'Date'[Date] ), 4, ISINSCOPE ( 'Date'[Month] ), 3, ISINSCOPE ( 'Date'[Fiscal Quarter] ), 2, ISINSCOPE ( 'Date'[Fiscal Year] ), 1 ) VAR MaxRevenue = CALCULATE ( SWITCH ( AxisLevel, 4, MAXX ( VALUES ( 'Date'[Date] ), [Revenue_Sum] ), 3, MAXX ( VALUES ( 'Date'[Month] ), [Revenue_Sum] ), 2, MAXX ( VALUES ( 'Date'[Fiscal Quarter] ), [Revenue_Sum] ), 1, MAXX ( VALUES ( 'Date'[Fiscal Year] ), [Revenue_Sum] ) ), ALLSELECTED ( 'Date' ) ) VAR MinRevenue = CALCULATE ( SWITCH ( AxisLevel, 4, MINX ( VALUES ( 'Date'[Date] ), [Revenue_Sum] ), 3, MINX ( VALUES ( 'Date'[Month] ), [Revenue_Sum] ), 2, MINX ( VALUES ( 'Date'[Fiscal Quarter] ), [Revenue_Sum] ), 1, MINX ( VALUES ( 'Date'[Fiscal Year] ), [Revenue_Sum] ) ), ALLSELECTED ( 'Date' ) ) VAR CurrentRevenue = [Revenue_Sum] VAR Result = DIVIDE( CurrentRevenue - MinRevenue, MaxRevenue - MinRevenue ) RETURN ResultRegards