To display sales which is non zero for latest date

[Anonymous]

Hi,

I have a scenario where I have to display the sales for the latest week, in my data i have weekstart date and weekend date,

Since the latest weekstart date is 04-05-2020 and latest weekend date is 10-05-2020 I have sales value as 0 for this week as the week has not yet ended.

So i have to display sales for the previous week, whenever the sales value is 0, I should display the previous week sales i.e. 27-04-2020 to 03-05-2020 this week sales should be displayed.

Can anyone suggest me how to achieve this

[amitchandak · Accepted solution]

@Anonymous , You can actually calculate a week rank to get this week and last week data.

If (isblank([This Week]),[Last Week],[This Week])

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Refer : https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You could also use WEEKNUM() function to get week like RANKX(). Then refer to Amit's DAX.

[amitchandak · Accepted solution]

@Anonymous , You can actually calculate a week rank to get this week and last week data.

If (isblank([This Week]),[Last Week],[This Week])

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Refer : https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

[Anonymous]

Thank you so much for the help