How to use slicer value as a key or reference to the filter in measure ?

[Birinder]

Hi there,
I hope you all are doing good.
I have a data connected via direct query. 
There are around 36 tables, but we will focus only on 2 of them.
One is period table which contains the field "Period (L2)".
Second is Capital table which contains the field "Amount".
Now I wanted a difference between amount of one period to another, so for example to calculate the values difference from the period FA 21 and FA 20, I've used the following measure andit worked perfectly.

Year Minus =

var fa21 =

CALCULATE(

    [Amount],

    'Period'[Period (L2)] IN { "FA 2021" },

    ALL('Period'[period_id])

)

var fa20 =
CALCULATE(

    [Amount],

    'Period'[Period (L2)] IN { "FA 2020" },

    ALL('Period'[period_id])

)

var result =

(fa21-fa20)

return

result

Now this is all convenient for me but not for other people I share the file with.
What I want is maybe a slicer for both First and second period, which will directly refer the period filter in measure.
For example: In place of 

'Period'[Period (L2)] IN { "FA 2020"}

we could select a period from the slicer and then it would appear in place of FA 2020, like:

'Period'[Period (L2)] IN {slicer value}

That will definitely help and it will make it more dynamic.
Is there a specific way to achieve this.
If you think I am going the wrong way and you have a specific solution for this, please feel free to share.
I will be very thankful to you all.

[PaulDBrown]

See if this thread helps (see the solution using slicers at the end)

https://community.powerbi.com/t5/Desktop/Waterfall-data-Value-Change-in-Matrix/td-p/2388951 

[Birinder]

Hi @PaulDBrown 
I've thoroughly checked the solution you provided, I dont know how to explain it but I am unknown how the database administrators has created this data and on what basis, as the field I want to use is showing it as a measure, The calculator sign in front of the field. When I click on it to see the code, Nothing appears.
Maybe that is the reason in SUM COST A, the sum expression doesnt gets applied, neither in SUM COST B.
I tried it to do without using the SUM expression, but the matrix is coming up empty.
I dont know what is the issues.
Maybe because my data is little bit different.
I have live data with direct query added. Period is in different dimension and capital is in different dimension. The other 2 attributes which act as row value and header value are also from different different dimensions. REAL PAIN IS I CAN'T EVEN SHARE THE SAMPLE DATA.


IF you want to get more knowledge of my data, you can see the replies given to vojtechsima above.
I've tried to elaborate my problem in the best way possible.

[vojtechsima]

Hi, @Birinder ,
you can incorporate "PREVIOUSYEAR" function and "MAX" function. 
It should work like this:
In your Current year measure, you use MAX(Date) as the filter (this will take max Date value from your Slicer), for this, you need to have a Date column.
Then in your next measure, you will just use PREVIOUSYEAR as the filter for Date and it should take the previous Year from the Slicer filter context.

[Birinder]

Hi @vojtechsima 
Thank you so much for reply.
I have actually tried modifying the measure as you said.
But the results are not coming right.
It would be really cool and appreciable, if you can write me the code.
Just an FYI, I have already created 2 seperate columns for current and previous periods.

[vojtechsima]

Hi, @Birinder ,
Sorry for the late reply, I tested the proposed thing but it didn'T work as I expected, so I created a measure for you that will deal with your situation, given you have Date Table:

CurrentYear Sales = 

var LatestDateInCurrentYear = CALCULATE(MAXX('Date','Date'[Date]), ALLEXCEPT('Table','Date'[Date]))
var EarliestDateInCurrentYear = CALCULATE(MINX('Date','Date'[Date]), 'Date'[Year] = YEAR(LatestDateInCurrentYear))

var LatestDateInPreviousYear = CALCULATE(MAXX(FILTER('Date','Date'[Year] = YEAR(LatestDateInCurrentYear)-1),'Date'[Date] ), ALLEXCEPT('Table','Date'[Date]))
var EarliestDateInPreviousYear =CALCULATE(MINX('Date','Date'[Date]), 'Date'[Year] = YEAR(LatestDateInPreviousYear))

var _currentYear = CALCULATE(SUMX(FILTER('Table', 'Table'[Date] <= LatestDateInCurrentYear && 'Table'[Date] >= EarliestDateInCurrentYear), 'Table'[Amount]))
var _previousYear = CALCULATE(SUMX(FILTER('Table', 'Table'[Date] <= LatestDateInPreviousYear && 'Table'[Date] >= EarliestDateInPreviousYear), 'Table'[Amount]))

var __calc = _currentYear - _previousYear

return  __calc

[Birinder]

hi @vojtechsima 
No issues for replying late and thanks a lot for answering.
I really appreciate it.
But one thing I am thinking of is that maybe you misunderstood the period column values.
The solution that you have referred to is for datetype date column.
I have a period column but it all have categorical values. There is no way I can put them as a slider slicer.


Like, see this picture (Ignore tables above the period cards). Here I want to reference these two categorical values AKA period values in my measure stated very above.
So that If I make selection of FA 2020 in left slicer and Q3 2019 in right one, it should give me the difference of values from FA 2020 and Q3 2019, The same way I am calculating in the code:

Year Minus =

var fa21 =

CALCULATE(

    [Amount],

    'Period'[Period (L2)] IN { "FA 2021" },

    ALL('Period'[period_id])

)

var fa20 =
CALCULATE(

    [Amount],

    'Period'[Period (L2)] IN { "FA 2020" },

    ALL('Period'[period_id])

)

var result =

(fa21-fa20)

return

result

I am really sorry for all the mess, If you think that I am getting it wrong. Then please change my perspective. I really appreciate you for sticking along.

[vojtechsima]

Hi, @Birinder 
If you have separated columns for the periods, that should be simpler:

CurrentVsPrevious = 
var Period_Current = SELECTEDVALUE(Period[Period_Current])
var Period_Previous = CALCULATE(SELECTEDVALUE(Period[Period_Previous]), ALL(Period[Period_Current]), USERELATIONSHIP('Table'[Period],Period[Period_Previous]))

var sum_current = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'), 'Table'[Period] = Period_Current))
var sum_previous = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'), 'Table'[Period] = Period_Previous))

var difference_calc = sum_current - sum_previous

return difference_calc

However, if you can't show sample data, try to re-create them with random values but keep the formatting as you would have in your table, just put it together in Excel and Paste it here, it will really help. Thank you

[Birinder]

Hi @vojtechsima 
Thanks for the reply. I've tried this solution, the table is coming blank. Is there anything I am missing. I want to confirm few things.

1. When we used USERELATIONSHIP function, what kind of relation do we need to establish, I created one to one dummy period table with original table.

2. Is there any need to edit interactions from format pane. I am using the default method.

And sorry, I cant upload files on Onedrive due to my organisation restrictions.

However I can give you brief explanation how things are in it.
* The data is connected live with direct query enabled.
* Each and every data table is related to each other.
* Amount column is in Capital table.
* Period column is in Period (D) table.

* I am using an attribute for column field as well which is named as "Layer" from "Analysis" table.
* There are 3 filters being applied on the page and they each are from different tables as well.
* I dont know how to explain it but I am unknown how the database administrators has created this data and on what basis, as the Amount field that I want to use, is showing as a measure, The calculator sign in front of the field. When I click on it to see the code, Nothing appears.

Thank you so much for your constant help. See if there is anything you can do.

[vojtechsima]

Hi, @Birinder ,
The relationship is like this:

And for the sample data, you can just paste here, you know like this for example:

DateAmountPeriod

01.01.2021	50	ALL2021
31.01.2021	50	ALL2021
10.06.2020	10	ALL2020
07.08.2020	10	ALL2020
01.09.2019	99	ALL2019

just create random values with corresponding logic and that's all I need.
I just need the amount column or measure, doesn't matter that much and the periods.

[Birinder]

@vojtechsima 
Okay Okay I will try my best.
Now one thing is to focus each table has around 30-40 column, but I will only give you the necessary and common columns.

First the "Period (D)" table, under which "period" will be used as a basis for difference. It is categorical text data type values.

period_id	Period
20204000	FA 2021
20210011	FA 2020
20210021	Q3 2019
20210031	Q2 2021
20210300	Q4 2019
20210600	Q1 2020
20210900	Q2 2022
20214000	Q3 2022
20220011	FA 2019

Next table is "Capital" under which "Amount" will be used as values. Amount here is shown as measures, I still dont know why database administrators has decided to do so. 

period_id	Amount	Organisation ID	Junk Dimension ID
20204000	150414983	15	52
20210011	1550443577	12	24
20210021	3769657488	5	51
20210031	2835732574	8	60
20210300	1328422901	16	49
20210600	1897812930	5	24
20210900	2517723972	14	79
20214000	4316809220	9	85
20220011	2613397245	3	27

Next table is "Division" under which "Division" is being used as row values:

Organisation ID	Division
15	Asia
12	America
5	Latin
8	Pacific
16	European
5	Japanese
14	Egypt
9	Texas
3	Lebron

Next table is "Analysis" under which "Layers" are being used as Columns:

Junk Dimension ID	Layers
52	Explained
24	Explained
51	Explained
60	Explained
49	Unexplained
24	Unexplained
79	Unexplained
85	Unexplained
27	Unexplained

There are 7 filters that are being applied on here, which are connected indirectly with all these tables. It will take eternity if I will start explaining how they are related to each other, However if you are still interested in knowing, I can spare my time.
 I think you can now provide me a sample PBIX file. 
Rest all the explanation on how I need my thing done is given up above. 
I just cant thank you enough.
Regards.

[vojtechsima]

Hi, @PaulDBrown 
any idea with stated tables?

[PaulDBrown]

@vojtechsima @Birinder 

To be able to have two slicers you need 2 period tables. 
The way to set it up is shown in this thread:
https://community.powerbi.com/t5/Desktop/Waterfall-data-Value-Change-in-Matrix/td-p/2388951