How to get Yesterday TOp 5 Sales by Category ?

[Anonymous]

I have a Date slicer with certain date range, balance and Product code.

So i have tried everything that i found in google but all failed.

I am just trying to get Top5 Product COde  (Yesterday Only) Yesterday only.

Yesterday= max (date)-1  (depending on date range slected date slicer)

Any one? Pelase help me. 🙂

Thanks in Advance
PBIX File 

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

We can create a date table and a measure to meet your requirement.

1. Create a date table and there is no relationship between two tables.

Date = CALENDARAUTO()

2. Then we can create a measure, and use the measure to create a table visual.

Measure = 
var _today = MAX('Date'[Date])
var _yesterday = _today - 1
var _result = CALCULATE(SUM(Sheet1[BalanceFCY]),FILTER(Sheet1,Sheet1[DataDate]=_yesterday))
return
_result

3. We need to configure the TOPN in filters on this visual, put the measure in ProductCode field.

4. At last we create a date slicer and the result like this,

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[amitchandak]

@Anonymous , Try creating Rank on measure like this and check

Last Day = CALCULATE(sum('order'[Qty]), previousday('Date'[Date]))

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415