Help with a measure/column to compare values from filters

[MCacc]

Hello, 

I'm trying to create a mesure/column (whatever works), to retrieve a comparison value between columns in a matrix visual. 

I have 4 slicers in total and in each slicer has the same date field.

- This Date field comes from a dummy,

- Meanwhile the values I want to compare come from my fact table. These values are strings, not numbers.

The two tables (this dummy and the fact table) are disconnected and can't be in a relationship.

Because my user can select a maximum of 4 values, this is what I did:

I created this dummy table where I have two columns: Index and Date

- Column Date is nothing more than the distinct value of my Date in the fact table

- Column Index is basically generated for each distinct value of of my Date 

This is the result:

IndexREFERENCEDATE_DT

1	31/03/2022
1	30/06/2022
1	31/12/2021
1	31/12/2022
2	31/03/2022
2	30/06/2022
2	31/12/2021
2	31/12/2022
3	31/03/2022
3	30/06/2022
3	31/12/2021
3	31/12/2022
4	31/03/2022
4	30/06/2022
4	31/12/2021
4	31/12/2022

Then I created 4 slicer objects in my page, and each slicer is filtered by its corresponding Index number.

Example for one of the slicers: 

I achieved this in order to work with my Index field, since the user can select whatever they want in any order they want.

What I'm really struggling with is to create a measure/column than can compare the values after the selection. 

So, for example: 

Date_1    Date_3 

Value RED    Value GREEN

..where, Date 1 is my first selection, Date 3 is my second selection. And this can happen in any order, not chronological. 

So, Value RED is worse than Value GREEN so my flag is negative . 

Something like that...

I tried EARLIER function, but the two tables must be in a relationship, as far as I understand. 

I also tried the function OFFSET, but it goes back of a specific number and this can't work because the order of selection isn't always Index - 1. 

Please, if you any idea, any suggestion/opinion.. anything is very well accepted. 

Thank you so much

[amitchandak]

@MCacc , Seem like you want compare two dates and two index , 1 set of each. You will need 4 disconnected tables 

//Date1 and Index 1  is independent tables
new measure =
var _date= maxx(allselected(Date1),Date1[Date])
var _index= minx(allselected(Index1),Index1[Index])
return
calculate( sum(Table[Value]), filter('Table', 'Table'[Date]= _date  && 'Table'[Index] = _index ))

//Date2 and Index 2  is independent tables
new measure =
var _date= maxx(allselected(Date2),Date2[Date])
var _index= minx(allselected(Index2),Index2[Index])
return
calculate( sum(Table[Value]), filter('Table', 'Table'[Date]= _date  && 'Table'[Index] = _index ))

Power BI Abstract Thesis: How to use two Date/Period slicers

https://youtu.be/WSeZr_-MiTg

Compare Categorical Data Using Slicers - Compare two Brands: https://youtu.be/exN4nTewgbc

[MCacc · Accepted solution]

Hi, thank you for your suggestion but unfortunately I can't use more than one measure since my visual is matrix type. 

Is there any way to use one measure/column instead of four?

Thanks anyway.