IF CURRENT WEEK SALE IS EMPTY

[Anonymous]

if current week sale is empty then need to calculate sales for the last non empty week sales. (for example if the current week is week number 5 and it doesnot have any sales data and week 2 does then i want it to show week 2 sales )
used following measure to calculate currweek sale:

payment currweek = CALCULATE(SUM('Table 3'[Payment]),FILTER(tbl_Calendar_ph,YEAR(tbl_Calendar_ph[Date])=YEAR(NOW())&&WEEKNUM(NOW())=tbl_Calendar_ph[Week Number]))

(PAYMENT IS SALES)
need something similar for previous week sales also
 THANKS for help.

[AlexisOlson · Accepted solution]

You could use the last sale date instead of NOW().

Try this:

payment currweek =
VAR LastSaleDate =
    CALCULATE (
        MAX ( tbl_Calendar_ph[Date] ),
        FILTER ( tbl_Calendar_ph, CALCULATE ( SUM ( 'Table 3'[Payment] ) ) > 0 )
    )
RETURN
    CALCULATE (
        SUM ( 'Table 3'[Payment] ),
        FILTER (
            tbl_Calendar_ph,
            YEAR ( tbl_Calendar_ph[Date] ) = YEAR ( LastSaleDate )
                && WEEKNUM ( LastSaleDate ) = tbl_Calendar_ph[Week Number]
        )
    )

[Anonymous]

can i get same logic for previous week?

[AlexisOlson]

Try appending " - 7" to the LastSaleDate variable.

[Anonymous]

YOU ARE THE BEST