Dynamic Matrix with 3 levels of filter

[Anonymous]

Hello,

I would like to know how I can build a matrix with values of the last 3 years depending on a year filter.

I’ve got a Date Table and the following measures :

• Measure for year N
• Measure for year N-1
• Measure for year N-2

I had to do this due to client constraints or my powerbi knwoledge.

I first tried to create one mesure that I could broke down year by year using a date column. It did not fit client requirements.

 As a result you can see below the formula of N-2 measure :

Cotisation brutes is a similar concept as sellings.  

Cotisations brutes N-2 =
CALCULATE(
SUM('Requête1'[cotisations_brutes]);
FILTER(
ALL(MonthTable_Soins[Year-Month]);
MonthTable_Soins[Year-Month] <= MAX(MonthTable_Soins[Year Month N-2])
);
FILTER(
ALL(MonthTable_Soins[Year-Month]);
YEAR(MonthTable_Soins[Year-Month]) = YEAR(MAX(MonthTable_Soins[Year Month N-2]))
);
FILTER(
ALL('MonthTable_Compt'[Year-Month]);
YEAR('MonthTable_Compt'[Year-Month]) <= YEAR(MAX('MonthTable_Compt'[Year-Month]))))

The trick here is to calculate the measure with 2 separtes date table faking a « SAMEPERIODLASTYEAR » DAX Formula.

This all works year by year, in separate vizualisation, but I can’t show i a same matrix this 3 different measure, which is a client requirement.

Exemple : Matrix for 2018

When I add year N-1 i’ve got this result :

As you can see, 2017 is above 2018 and not showing the proper figures.

Basically, I would like a matrix which provide me a visualisation like below :

		Cotisation (Formula)
2016	Non cadre	Value
Cadre	Value
2017	Non cadre	Value
Cadre	Value
2018	Non cadre	Value
Cadre	Value

Many Thanks,

[kentyler]

Could you say more about the client requirement.

If you had a slicer or a parameter specifying the start year, in DAX you could calculate the 3 year interval you want and then do your sums against the whole date range.

When you display the values in a tablular control it should break them down into different totals for each year.
It's hard for me to speculate on just how to achieve this, but I'd be glad to do a screen share and take a look at it with you. Email me a time and day and I'll set up a meeting.