Forum Discussion
Solved
Matrix Visual: Combing Two Calculations and Fields Into Visual
Hi all, I hope you're all doing well. I have a task where I need to showcase two calculations on a Matrix Visual: A Yearly Calculation for the ones in Green A 12 Month Trail...
Hi v-saisrao-msft, I'm looking into the Paginated Report solution and learning about it first. I will revert back once I have designed the view. Thank you!
I think the easiest way would be to create a calculation group with calculation items for each column, something like
Year - 2 =
VAR LastDateWithValues =
CALCULATE ( MAX ( Sales[Date] ), REMOVEFILTERS () )
VAR Result =
CALCULATE (
SELECTEDMEASURE (),
'Date'[Year]
= YEAR ( LastDateWithValues - 2 ),
REMOVEFILTERS ( 'Date' )
)
RETURN
Result
and
Month - 2 =
VAR LastDateWithValues =
CALCULATE ( MAX ( Sales[Date] ), REMOVEFILTERS () )
VAR RefDate =
EOMONTH ( LastDateWithValues, -2 )
VAR DatesToUse =
DATESINPERIOD ( 'Date'[Date], RefDate, -12, MONTH )
VAR Result =
CALCULATE ( SELECTEDMEASURE (), DatesToUse )
RETURN
Result
The key is to take the MAX of your fact table date column, not of 'Date'[Date].
You could also create a measure for the month name labels like
Label =
FORMAT ( MAX ( 'Date'[Date] ), "mmmm" )