Return column X values of prev week if prev week column Z is empty

[BYENER]

Hi All,

I need your help. If the prev week is empty (column A, B) than I want to return the last week values in column E, F, G, H.

Example: The prev week of week 6 is empty so I want to copy the values of week 5 (column E,F,G,H) to the column E,F,G,H of week 6. Is this possible? To summarize copy the values of E,F,G,H to all weeks that come after the week with values + 7 days.

I hope that my explanation is a little bit clear for  you.

[Anonymous]

Hi  @BYENER ,

According to my understanding, you want to get the value of E in previous week when the sum value on A is blank this week ,right?

You could use the following formula to create a new column:

Year-Week =
FORMAT ( [Date], "YYYY-WW" )

Column =
VAR _sum =
    CALCULATE ( SUM ( 'Table'[A] ), ALLEXCEPT ( 'Table', 'Table'[Year-Week] ) )
RETURN
    IF (
        _sum = BLANK (),
        CALCULATE (
            SUM ( 'Table'[E] ),
            FILTER ( 'Table', 'Table'[Date] = EARLIER ( 'Table'[Date] ) - 7 )
        ),
        [A]
    )

The final output is shown below:

Please take a look at the pbix file here.

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

[BYENER]

Hi @Anonymous , 
Thank you for your reply and example pbix file, but when I do the same in my report than I get the next error. It don't takes the date column while this table and column is exist, very strange.

[Anonymous]

Hi @BYENER ,

I used EARLIER() function in calculated column not measure.

Best Regards,
Eyelyn Qin

[amitchandak]

@BYENER , These value are not of same type.

You need a week before or Week on week

example

This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
7 behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-7,DAy))

Measure = if(isblank([This Day]), [7 behind Sales] ,[This Day])

Or do same with this week and last week 

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8