Return last week value if there is no value

[BYENER]

Hi,

I have a matrix with values per day. How can I return the last week values to another week where I don't have value (blank)?

For example column B:  4/1/2021 is empty so I will return the value from 28/12/2020 (16748) to 04/01/2021. 

Thanks in advance.

[Anonymous]

Hi @BYENER ,

I made a test file myself, please refer to it.

Measure = 
IF (
    ISBLANK ( MAX ( 'Table'[Value] ) ),
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), [Date] = MAX ( 'Table'[Date] ) - 7 )
    ),
    MAX ( 'Table'[Value] )
)

Best Regards,

Stephen Tao

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

[BYENER]

Hi @Anonymous ,

This works perfect! But I will copy this value not only to next week but to all weeks in the future what is blank. How can I do that?

it's filled till 15/01 but I want till 27/01

Kind regads

Bahar

[amitchandak]

@BYENER , 7 days before or last week

Assume you have measure sales

if(isblank([sales]), CALCULATE(([Sales]),dateadd('Date'[Date],-7,DAy)),[Sales])

You can do the same for this week vs last week. Refer to my blog for that

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

[BYENER]

I used your formula, but I get this (last column)

[amitchandak]

@BYENER , you are using date table and the week is from the date table?

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[BYENER]

@amitchandak 

Yes, its from the the date table.

See example pbix. https://www.dropbox.com/s/4jbsrruvdjhin2w/test.pbix?dl=0