need to extend the quantities across all dates

[sajal161292]

Hi,

I am working on a visual in Power BI in which I woould like to copy the previous week's quantity to the next week if it does not exists and keep the same values if it exists.

As an example in the 1st row there is no quantity against the dates 2/26/2018 and 2/27/2018...so it should be previous week's value(0 11359 11359) etc.. 

 

Need to create  a DAX measure for this functionality.

So could you please tell me a way of achieving it in Power BI?

[v-jiascu-msft]

Hi @sajal161292,

Please share a dummy sample. We can't write a DAX formula without data and its structure. Maybe you can try the function LASTNONBLANK. 

There will be many questions without data.

1. Are all the columns from the same table?

2. Are the values from a table or from calculations?

3. What are the relationships?

Best Regards,

Dale

[sajal161292]

Hi,

Please find the link of my file below:

https://drive.google.com/open?id=1cCnbdHqfSGa4q_ktrzcPiYv1dM8GrAZj

Thanks

[v-jiascu-msft]

Hi @sajal161292,

PLease try the steps below.

1. Add a new table.

Table = VALUES(Shortages[Due Date])

2. Create a new measure.

Measure =
VAR lastday =
    CALCULATE (
        MAX ( 'Shortages'[Due Date] ),
        FILTER (
            ALL ( 'Table'[Due Date] ),
            'Table'[Due Date] < MAX ( 'Table'[Due Date] )
        )
    )
RETURN
    IF (
        ISBLANK ( MIN ( 'Shortages'[Due Date] ) ),
        CALCULATE (
            SUM ( Shortages[quantity] ),
            FILTER ( ALL ( 'Table'[Due Date] ), 'Table'[Due Date] = lastday )
        ),
        SUM ( Shortages[quantity] )
    )

Please be aware of your privacy.

Best Regards,

Dale

[sajal161292]

Hi,

I followed these steps but it is giving incorrect results to me.

So could you please check it from your end ?

[sajal161292]

It worked now.Thanks for your help!!

But actually the requirement has changed now.

Now the purpose is to retain the total field at the bottom with the previous values in case of missing quantitites for any date.

This total is basically thr running total field(which adds up the quantities across all the order types).

Please find the link of my visual attached below:

https://drive.google.com/open?id=1cCnbdHqfSGa4q_ktrzcPiYv1dM8GrAZj