Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
hi, i have this Table1 that shows reservehist changes occured on each datehist
DateHist | ReserveHist |
02/05/2023 | 505 |
03/05/2023 | 11005 |
15/05/2023 | 6505 |
02/06/2023 | 8007.2 |
14/06/2023 | 8207.2 |
26/06/2023 | 8092.2 |
28/06/2023 | 7867.2 |
how do i create the column on the right below? i need this column to read from the table above - for example, 17/05/2023 falls between 15/05 and 02/06, that means the value should be 6505.
the last change happened on 28/06, so any dates below after 28/06 should show 7867.2
Date | How to Create This Column? |
17/05/2023 | 6505 |
18/05/2023 | 6505 |
02/06/2023 | 8007.2 |
14/06/2023 | 8207.2 |
26/06/2023 | 8092.2 |
28/06/2023 | 7867.2 |
27/07/2023 | 7867.2 |
22/08/2023 | 7867.2 |
does anyone have any ideas?
Solved! Go to Solution.
Actually i resolved it myself taking a direction from your query - thanks so much was very helpful.
for anyone interested, this was the solution that worked perfectly.
i jsut needed to add an additional ID column as well to the tables, but you can just remove those bits from this query below and use it.
@rushkarausch ,Try using below method
ReserveHistValue =
VAR SelectedDate = SELECTEDVALUE('DateTable'[Date])
RETURN
CALCULATE(
LASTNONBLANK('Table1'[ReserveHist], 1),
FILTER(
ALL('Table1'),
'Table1'[DateHist] <= SelectedDate
)
)
Proud to be a Super User!
thanks for your reply, but this didnt work sadly 😞 any suggestions why?
Actually i resolved it myself taking a direction from your query - thanks so much was very helpful.
for anyone interested, this was the solution that worked perfectly.
i jsut needed to add an additional ID column as well to the tables, but you can just remove those bits from this query below and use it.
User | Count |
---|---|
50 | |
24 | |
18 | |
17 | |
14 |
User | Count |
---|---|
91 | |
86 | |
39 | |
25 | |
21 |