Ranking Top 10 for Past 7 Days

[mamsteroonie]

How do I rank a sum of a value utilizing a date to calcluate the past 7 days? 

Currently I have a calc column:

ThisWeekLastWeek = IF([Local Date]>=TODAY()-7,"This Week",IF(AND([Local Date]>=TODAY()-14,[Local Date]<TODAY()-7),"Last Week"))

Then I convert to a measure ThisWeekLastWeek1= Max(ThisWeekLastWeek)

Then I do the ranking:

Ranking = IF([ThisWeekLastWeek1]="This Week",RANKX(ALLSELECTED(Table[Name]),CALCULATE(SUM(Table[Value]))),10000)

I get the correct ranks for the top 4 or 5 but then it does not correctly rank for the last 5... please help!

[Anonymous · Accepted solution]

Hi,

I went about it this way: rather than creating a week column like you did, I simply created a measure for "Sales Last 7 Days":

Sales Last 7 Days = SUMX(FILTER(sales, DATEDIFF(sales[Date],MAX(sales[Date]),DAY) < 7),sales[Revenue])

I used a sample PowerBI Dashboard in a Day dataset, so I used MAX(sales[Date]) as the comparison, rather than something like TODAY() as the dataset is somewhat stale. From there, you can simply use the "Top N" filtering functionality on whatever dimension you're ranking (in my case I used ProductID).

Let me know if this helps.

Ben

[Anonymous · Accepted solution]

Hi,

I went about it this way: rather than creating a week column like you did, I simply created a measure for "Sales Last 7 Days":

Sales Last 7 Days = SUMX(FILTER(sales, DATEDIFF(sales[Date],MAX(sales[Date]),DAY) < 7),sales[Revenue])

I used a sample PowerBI Dashboard in a Day dataset, so I used MAX(sales[Date]) as the comparison, rather than something like TODAY() as the dataset is somewhat stale. From there, you can simply use the "Top N" filtering functionality on whatever dimension you're ranking (in my case I used ProductID).

Let me know if this helps.

Ben

[mamsteroonie · Accepted solution]

Thanks for the quick reply.  I ended up using a measure as you mentioned.  But what really helped me was using a date dimension to calculate the last 7 days.  I have noticed that whenever I'm using time for anything, my data is skewed if I dont use a date table.  That helped me get the right values...then I needed to rank them - and I used the Top N function for the CATEGORY, (not the measure itself which stumped me for a while since it was not available at the measure level).

Thanks again!