Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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/20...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
vanessafvg
Icon for Community Champion rankCommunity Champion
6 years ago
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's avatar
    Anonymous
    Not applicable
    6 years ago

    hi vanessafvg,

     

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

     

    thanks,

    Michael