Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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] )
Please see file attached as well
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |