CurrentYear, PriorYear and difference of these in Matrix

[Anonymous]

Dear all,

I'm searching for the solution of the problem:

1) I have one measure based on value of cost per period, per year:

When creating Matrix table consisting of:

the result is what I expect:

But I need another row with difference between 2022 and 2021 below the existing two. And here comes the problem as I've tried many solutions, but I still didn't find how to make it work. 

Anyone have an idea how to make it work? I would really appreciate help.

[Anonymous]

Hi  @Anonymous ,

I created some data:

You can create a calculation table because you want to add a row between 2021 and 2022, the default ordering of power bi is by ABC order of characters, so we need to name him 2021-all_actual_twoyear so that it can be displayed in the middle

The result of adding a new line is the calculated value with [Period] as the grouping

Here are the steps you can follow：

1. Create calculated table.

Table_true =
var _1=
SUMMARIZE('Table',
'Table'[Period],'Table'[Actual/Plan],'Table'[Value in USD])
var _2=FILTER(_1,
'Table'[Actual/Plan]="Actual")
var _summarize=
SUMMARIZE(
    'Table',
    [Period],
    "Year" , "2021-all_actual_twoyear",
    "Actual/Plan","Actual",
    "Value in USD" ,
SUMX(_2,[Value in USD]))
    return
    UNION('Table',_summarize)

2. Create measure.

Measure =
CALCULATE(SUM('Table_true'[Value in USD]),
FILTER(ALL('Table_true'),'Table_true'[Year]=MAX('Table_true'[Year])&&'Table_true'[Period]=MAX('Table_true'[Period])&&'Table_true'[Actual/Plan]="Actual"))

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hi,

thanks for help. 

I'm trying to implement that solution on my dataset, but somehow it's not working:

my dataset is consisting of totally 45 columns in the following structure (first 20):

where I, indeed, have Year, Period and Value.

So when I try your solution, I need to create calculated table with following DAX:

Table_for_delta = 

var _1 = 

SUMMARIZE('CCBU database','CCBU database'[Period],'CCBU database'[Date_year],'CCBU database'[Actual/Plan],'CCBU database'[Value in USD])

var _2 =FILTER(_1,'CCBU database'[Actual/Plan]="Actual")

var _summarize =

SUMMARIZE('CCBU database',

'CCBU database'[ID], 'CCBU database'[SGA],'CCBU database'[DivS_Shrd],'CCBU database'[Division],'CCBU database'[SubDivL1],'CCBU database'[SubDivL2],'CCBU database'[Market],'CCBU database'[Submarket],'CCBU database'[Country],'CCBU database'[Legacy],'CCBU database'[Entity],'CCBU database'[EntityEPM],'CCBU database'[CostCenter],'CCBU database'[CostCenterName],'CCBU database'[CostCenterOwner],'CCBU database'[Function],'CCBU database'[Line],'CCBU database'[Currency],'CCBU database'[CostElementL1],'CCBU database'[CostElementL2],'CCBU database'[CostElementL3],'CCBU database'[CostElement],'CCBU database'[CostElementName],'CCBU database'[EPMAccount],'CCBU database'[EPMCategory],'CCBU database'[EPMCostCenter],'CCBU database'[Year],'CCBU database'[FX_rates.Direct Quote],'CCBU database'[Division_conc],'CCBU database'[Division_mapping.DivisionTag],'CCBU database'[Country_conc],'CCBU database'[Country_conc.1.Country Alloc.],'CCBU database'[Country_conc.1.MegaMarket],'CCBU database'[Country_conc.1.Market],'CCBU database'[Period],'CCBU database'[Value in LC Currency],'CCBU database'[FiscalMonth],'CCBU database'[Date], 'CCBU database'[IsCurrentYear],'CCBU database'[Help_PYTDmeasure],'CCBU database'[Year_Period_Conc],'CCBU database'[DeltaYoy_Help],

"Date_year", "2022 Delta",

"Actual/Plan", "Actual",

"Value in USD",

SUMX(_2,[Value in USD]))

return

UNION('CCBU database',_summarize)

but in the end I get the table which is not correct - I have periods in the wrong column for example:

or fx rate in Actual/Plan column:

How to make it work? Also I wondered if such a big calculated table is a good option in terms of efficiency.