Adding rows based on criteria

[kiddn]

Hi,

I currently have a list of exchange rates dating back a number of years. Given the markets are closed Saturday and Sunday, there are no values associated with these dates. Format is row by row.

Is it possible to fill in these  'gaps' by looking at the previous date entered with a value. An example is below:

Friday 27/7/18         EUR NZD             0.5815

Saturday 28/7/18     EUR NZD             no value

Sunday   29/7/18     EUR NZD             no value

Monday 30/7/18      EUR NZD            0.5820

Can Power BI add rows to fill in last value available i.e. 0.5815? Note Saturday and Sunday dates do not exist in the data set so the formula would need to be able to write in Saturday and Sunday and the value associated with it.

Any help appreciated.

Thanks

[v-juanli-msft · Accepted solution]

Hi @kiddn

Note Saturday and Sunday dates do not exist in the data set so the formula would need to be able to write in Saturday and Sunday and the value associated with it.

Since you dataset doesn't contain Saturday and Sunday, you need to add them to your dataset.

1.In Power Query, create a date table, reference here about how to create a date table in power query.

2.create custom columns in your dateset and the date table to define weekday of each date.

use function

Date.DayOfWeek([Column1])

3.in the newly created date table, use "Merge queries", then expand columns

4.sort the column(date) by "sort ascending", then click "fill down" for column "Sheet6.data"

 

Best Regards

Maggie

[Zubair_Muhammad]

@kiddn

Hi,

Try adding this calculated column

Revised Values =
VAR NonBlankRow =
    TOPN (
        1,
        FILTER (
            Table1,
            Table1[Currencies] = EARLIER ( Table1[Currencies] )
                && Table1[Date] <= EARLIER ( Table1[Date] )
                && NOT ( ISBLANK ( Table1[Value] ) )
        ),
        [Date], DESC
    )
RETURN
    MINX ( NonBlankRow, [Value] )

[Zubair_Muhammad]

@kiddn

Please see file attached as well

[kiddn]

Hi thanks for your support.

To clarify, are your revised values based off dates that are already there? i.e. a master date table.  Or is DAX smart enough to add in the extra dates as rows then calculate the values from that?