show data on two dates in one table

[Anonymous]

hi experts,

I have a dataset like below,

Date	ProductId	Category	Amount
10/31/2019	101	A	10
10/31/2019	102	A	12
10/31/2019	103	B	110
12/31/2019	101	A	15
12/31/2019	102	A	14
12/31/2019	104	C	20
4/30/2020	101	A	15
4/30/2020	102	B	110
4/30/2020	105	B	50
...	...	...	...

Basically, on different dates, we have some products, and each product is categorized into groups, however the categorization can change from date to date. For example in above table, product 102 was in Group A on 10/31/2019, but in Group B on 4/30/2020

Now I want to create a dashboard, in this dashboard, I need two filters on date, Date1 and Date2, so users can choose two dates to compare. For example, I can choose 4/30/2020 in Date1 filter and choose 10/31/2019 in Date2 filter, then I want to show data in a table in below format:

ProductId	Date1 Category	Date1 Amount	Date2 Category	Date2 Amount
101	A	10	A	15
102	A	12	B	110
103	B	110
105			B	50

How can I achieve this?

Thanks,

Michael

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

You will need to create two slicer tables and four measures.

slicer1 = DISTINCT('Table'[Date])

slicer2 = DISTINCT('Table'[Date])

Date1 Category = CALCULATE(SELECTEDVALUE('Table'[Category]),FILTER(ALLSELECTED('Table'),'Table'[Date]=SELECTEDVALUE(slicer1[Date])&&'Table'[ProductId]=SELECTEDVALUE('Table'[ProductId])))

Date1 Amount = CALCULATE(SELECTEDVALUE('Table'[Amount]),FILTER(ALLSELECTED('Table'),'Table'[Date]=SELECTEDVALUE(slicer1[Date])&&'Table'[ProductId]=SELECTEDVALUE('Table'[ProductId])))

Date2 Category = CALCULATE(SELECTEDVALUE('Table'[Category]),FILTER(ALLSELECTED('Table'),'Table'[Date]=SELECTEDVALUE(slicer2[Date])&&'Table'[ProductId]=SELECTEDVALUE('Table'[ProductId])))

Date2 Amount = CALCULATE(SELECTEDVALUE('Table'[Amount]),FILTER(ALLSELECTED('Table'),'Table'[Date]=SELECTEDVALUE(slicer2[Date])&&'Table'[ProductId]=SELECTEDVALUE('Table'[ProductId])))

Use the date column from these two slicer tables and the result would be shown as below.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[vanessafvg]

will there only ever be 2 categories?   

can group A happen and then group A again?

what is the rule for the sequence, what is the pattern?   is it a = 1, b  = 2, if so your data isn't showing that.

[Anonymous]

Hi,

Thanks for your reply.

There will be many categories, and the total number of categories are unknown, we can always have new categories too. 

A product can be categorized into Group A on day 1, and categorized to Group B on day 2, and categorized back to Group A on day 3, and so on. 

There's no pattern on how product is categorized. We just treat the category as an attribute of products.

Thanks,

Michael

[vanessafvg]

so the sequence / pattern is then the date, date 1 = 1 , date 2 = 2 so it needs to be ordered by date and it can have a variable amount of dates?

[Anonymous]

hi vanessafvg,

yes there can be variable amount of dates, right now we have one year dates.

thanks,

Michael