To display non-zero sales for the most recent date

[neelofarshama]

Hello

I have a scenario where I have to show the sales of the last week, in my data I have the start date of the week and the weekend date,

Since the last start date of the week is 04-05-2020 and the last weekend date is 10-05-2020 I have the sales value as 0 for this week, since the week is not yet over.

So I have to show the sales from the previous week, as long as the sales value is 0, I must show the sales of the previous week, that is, 27-04-2020 to 03-05-2020 this week the sales should be shown.

Can anyone suggest to me how to achieve this

[Icey · Accepted solution]

Hi @neelofarshama ,

Please check:

1. Sample fact table:

2. Create a Dates table.

Dates = 
ADDCOLUMNS (
    CALENDAR ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ) ),
    "Year", YEAR ( [Date] ),
    "WeekNum", WEEKNUM ( [Date], 2 )
)

3. Create relationships.

4. Create a measure.

Measure = 
VAR ThisWeek =
    CALCULATE ( MAX ( Dates[WeekNum] ), Dates[Date] < TODAY () )
VAR ThisWeekValue =
    CALCULATE ( SUM ( 'Table'[Value] ), Dates[WeekNum] = ThisWeek - 1 )
VAR WeekNum1 =
    MAX ( Dates[WeekNum] )
RETURN
    SWITCH (
        TRUE (),
        WeekNum1 < ThisWeek, SUM ( 'Table'[Value] ),
        WeekNum1 >= ThisWeek, ThisWeekValue
    )

BTW, .pbix file attached.

Best Regards,

Icey

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

[amitchandak]

@neelofarshama ,

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))

https://community.powerbi.com/t5/forums/replypage/board-id/power-bi-designer/message-id/498532