Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

How to order visuals by most recent date?

So my report is going to have a number of donut visuals. This donut visual will also have cards associated with it.

 

What I want to do is have the top left donut visual represent data related to the most recent date. The donut visual to the right of the top left donut would represent data related to the second most recent date, etc...

 

I'm really struggling on how to accomplish this. I thought to create an index column and then assign the first visual index 1, the second index 2, and etc...

 

This works, however, I would not be able to have filters on my report. If I had a filter, some visuals would be empty and such.

 

Does anyone have any ideas on how I could accomplish this type of report with filters on my report as well? I've racked my brain but am stumped. 

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    You can use formulas to control that. In case you do not want slicer to control

     

    Sales Today = 
Var _date =DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))
return
 CALCULATE(COUNT('Sales'[Sales Date]),'Date'[date]=_date)
 
 
Sales Yesterday = 
Var _date =DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())-1)
return
 CALCULATE(COUNT('Sales'[Sales Date]),'Date'[date]=_date)

     

     

    Controlled by Slicer

     

    Selected Date = 
Var _date_sel = CALCULATE(maxx('Date'[Date]),dateadd('Date'[Date],-1,day)) //change this
return
 CALCULATE(COUNT('Sales'[Sales Date]),'Date'[date]=_date_sel)

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks.

    My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks for the reply amitchandak . I don't think I can use the formula route since the dates the visuals would be using can be completely random in the dataset. The most recent date could be 11/26, the second most recent 11/20, the third 11/19, the fourth 10/30, etc...

       

      Also, from looking at the slicer option, that would just be adjusting the visual to whatever date you select in the slicer?

       

      I need a way to rank my data, but dynamically if filters are involved. 

       

      Is there a way to create a dynamic table based on slicer selections. So then a new table would be dynamically created in memory and then a ranking could be derived from there?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        HI Anonymous .

        Can you please share some dummy data with the same data structure and expected result for test? It is hard to coding formula without any detail records.

        How to Get Your Question Answered Quickly  
        Regards,

        Xiaoxin Sheng