Forum Discussion
Help: A customer row disappear on a matrix after adding another calculation group items
Hi Guys,
I need a help on below
I've created a matrix using 2 calculation groups:
1. time periods : such as LTM (last 12 months), LTM Prior year, qtd, ...
2. metrics : such as revenue, gross margin, ..
both of them are on slicers.
on the matrix rows well: parent customer and child customers
on the matrix columns well: time periods
on the matrix values well: LTM revenue, use to sort desc
the date filter was on 2026 - March
LTM DAX : CALCULATE(SELECTEDMEASURE(), DATESINPERIOD('DateTable'[Date], MAX('DateTable'[Date]), -12, MONTH))
LTM Prior: CALCULATE(SELECTEDMEASURE(), DATESINPERIOD(DateTable[Date],MAX(DateTable[Date])-12,-12,MONTH))
the issue that I am having is:
I know for the fact that customer A has revenue on LTM and LTM Prior
but if I select both of them to show them on the visual the customer disappear.
I noticed also the order is changed.
What did I do wrong? and how to fix them?
Thanks,
Hi Anonymous
Thank you for the reply, long story short, there were a main issue based on my experiences, sorting on matrix was unpredictable if involve more than 1 metric. So after trying few methods, the fix was to use disconnected table that act as ranking.
4 Replies
- Shai_Karmani
Super User
Mmmm...two things are happening here and only one is the formula.
The LTM Prior formula is buggy. Your anchor is MAX(DateTable[Date])-12, and subtracting a plain number from a date subtracts days, not months. So your "prior year" window is really just LTM shifted back 12 days, basically overlapping itself. Use EDATE instead:
LTM Prior =
CALCULATE(
SELECTEDMEASURE(),
DATESINPERIOD(
'DateTable'[Date],
EDATE( MAX( 'DateTable'[Date] ), -12 ),
-12,
MONTH
)
)
Your LTM measure is fine as is.
The disappearing row is almost certainly blanking. A matrix hides any row where every visible cell is blank. To confirm: in the format pane, turn on "Show items with no data" for the row field. If Customer A comes back with some blank cells, it's a blanking issue. Two usual suspects: no precedence set between your two calc groups (set Calculation Group Precedence on each in Tabular Editor, different numbers for each), or you're double-applying time logic (put your base [Revenue] in Values and let the calc group do the shifting, don't pre-shift an already-LTM measure).
The reorder happens because sorting by a value column gets shaky when a calc group lives on the columns. Add a dedicated sort measure that doesn't depend on the column selection:
Sort LTM Revenue =
CALCULATE( [Revenue], DATESINPERIOD( 'DateTable'[Date], MAX( 'DateTable'[Date] ), -12, MONTH ) )
Sort the visual by that and the order stays put.
Fix the EDATE bug first, then flip on "Show items with no data" to see if it's blanking or sorting. That one toggle tells you which thing to chase. Also peek for any Top N or "is not blank" filter hiding on the visual.
If this works for you, kindly mark it as the solution and give a thumbs up.
Best,
Shai Karmani
Unfortunately still not working, maybe lack of my knowledge, anyway below is how the visual looks like
The customer that missing is Khol's; it does show if I only either select LTM current or LTM Prior but if I select all of them then it become disappear. I have tried to put COALESCE on the measure so I think guarantee will not have blank.Hi luke_123 ,
Thank you for sharing the additional screenshot.Since LTM Current and LTM Prior both return the expected results when selected individually, it appears that the underlying data and customer relationship are working correctly. This suggests the behavior may be related to how the calculation groups are being evaluated when both calculation items are selected together.
To help narrow this down, could you try removing the sort measure from the matrix and check whether Kohls appears when both LTM Current and LTM Prior are selected? It would also be helpful to know the precedence settings for your Metrics and Time Period calculation groups, along with whether the matrix is using the base Revenue measure or a measure that already contains time intelligence logic.
That information should provide a better understanding of how the calculation groups and sorting logic are interacting and help identify what is causing Kohls to be excluded when both selections are applied.
Links provided here:Calculation groups in Analysis Services tabular models | Microsoft Learn
Create calculation groups in Power BI - Power BI | Microsoft LearnThank you.
Hi Anonymous
Thank you for the reply, long story short, there were a main issue based on my experiences, sorting on matrix was unpredictable if involve more than 1 metric. So after trying few methods, the fix was to use disconnected table that act as ranking.
