Forum Discussion
show data on two dates in one table
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
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.
5 Replies
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.
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
- 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?
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.
