How to do an inter calculation in matrix ?

Birinder , from where the fa20 , and q2 2021 is coming?

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Birinder , If you need diff between two period, separated by 1 period or one year.  With help from period/date table, Period rank and Year  , Period number you can do

 

example

new column in period or date table

Period Rank = RANKX(all(Period),Period[year period],,ASC,Dense)

 

measure
This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])))
Last Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])-1))

 

 

This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),'Period'[Year]=max('Period'[Year]) && Period[Period]=max(Period[Period])))
Last year same Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),'Period'[Year]=max('Period'[Year])-1 && Period[Period]=max(Period[Period])))

 

 

Actually, I am somewhat confused if you are looking for something other than period diff. Sorry for that

Hi, Birinder 

 

I recovered some data by the picture you gave me, and I hope it will restore your problem.

Measure = 
VAR n1 =
    CALCULATE (
        MAX ( 'Table'[Price] ),
        FILTER (
            'Table',
            [Period] = "Q2 2021"
                && [Currency] = MAX( 'Table'[Currency] )
                && [Methods] = MAX( 'Table'[Methods] )
        )
    )
VAR n2 =
    CALCULATE (
        MAX ( 'Table'[Price] ),
        FILTER (
            'Table',
            [Period] = "FA 2020"
                && [Currency] = MAX('Table'[Currency] )
                && [Methods] = MAX('Table'[Methods] )
        )
    )
RETURN
    n1 - n2

 

Best Regards,

Community Support Team _Charlotte

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

Hi, Birinder 

 

I simulated some more data, and I hope this time it fits your situation.

Use the function to create a new table.

Table = SELECTCOLUMNS(Dim_facts,"Currencies_ID",[Currencies_ID])

Add calculated columns using lookupvalue.

Price = LOOKUPVALUE(Dim_facts[Price],Dim_facts[Currencies_ID],[Currencies_ID])

Price = LOOKUPVALUE(Dim_facts[Price],Dim_facts[Currencies_ID],[Currencies_ID])

Junk_Dimension_id = LOOKUPVALUE(Dim_facts[Junk_Dimension_id],Dim_facts[Currencies_ID],[Currencies_ID])

Methods = LOOKUPVALUE(Dim_methods[Methods],Dim_methods[Junk_Dimension_id],[Junk_Dimension_id])

Period_id = LOOKUPVALUE(Dim_facts[Period_id],[Currencies_ID],[Currencies_ID])

Period = LOOKUPVALUE(Dim_period[Period],Dim_period[Period_id],[Period_id])

Measure just uses the function in the previous reply.

 

Best Regards,

Community Support Team _Charlotte

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

 

 

 

Hi, Birinder 

 

This source data is really too large.

 

By the same token, both Junk_Dimension_id and Period_id can be used as the columns selected for the new table.

Table = SELECTCOLUMNS(Dim_facts,"Junk_Dimension_id",[Junk_Dimension_id])

Table = SELECTCOLUMNS(Dim_facts,"Period_id",[Period_id])

Hope this helps you.

 

Best Regards,

Community Support Team _Charlotte

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

 

Hi, Birinder 

 

You can also start by selecting the Dim_Currency table.

Table = SELECTCOLUMNS(Dim_Currency,"Currencies_id",[Currencies_id])

 

Best Regards,