Forum Discussion

Zoran05's avatar
Zoran05
Frequent Visitor
2 years ago
Solved

Previous latest available Date

Hi community, 

 

I'm using the following formula to get the previous Day Price. The problem is that on a weekend or holiday I don't have a price. So on a Monday the calculation returns no value. 

 

Prev Day Price =
CALCULATE(
    [Current Price],
    DATEADD('Date Lookup'[Datum],-1,DAY))
 
I have the same problem for previous week or previous month Price. Your help 
 
This are the results I'm getting. 
Datum1 Day ReturnCurrent PricePrev Day Price
3/5/2024 0:000.00% 211.2
3/4/2024 0:00100.00%211.2 
3/1/2024 0:000.93%210.72208.75
2/29/2024 0:000.41%208.75207.89
2/28/2024 0:00-0.15%207.89208.21
2/27/2024 0:000.01%208.21208.18
2/26/2024 0:00100.00%208.18 
2/23/2024 0:000.31%208.4207.75
2/22/2024 0:001.75%207.75204.12
2/21/2024 0:00-0.15%204.12204.43
2/20/2024 0:00-0.38%204.43205.21
2/19/2024 0:00100.00%205.21 
2/16/2024 0:00-0.03%205.24205.3
 
Thank you very much for your help :).
best regards
Zoran
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Zoran05 

    You can try the follwing measure.

    Sample table.

    Prev Day Price =
VAR a =
    MAXX (
        FILTER (
            ALLSELECTED ( 'Date Lookup'[Datum] ),
            [Datum] < MAX ( 'Date Lookup'[Datum] )
                && [Current Price] <> BLANK ()
        ),
        [Datum]
    )
RETURN
    IF (
        [Current Price] <> BLANK (),
        CALCULATE ( [Current Price], 'Date Lookup'[Datum] = a )
    )

    Output

     

     

     

    Best Regards!

    Yolo Zhu

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

     

3 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    2 years ago

    i think it's the data from the table visual. could you pls proivde the sample data?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Zoran05 

    You can try the follwing measure.

    Sample table.

    Prev Day Price =
VAR a =
    MAXX (
        FILTER (
            ALLSELECTED ( 'Date Lookup'[Datum] ),
            [Datum] < MAX ( 'Date Lookup'[Datum] )
                && [Current Price] <> BLANK ()
        ),
        [Datum]
    )
RETURN
    IF (
        [Current Price] <> BLANK (),
        CALCULATE ( [Current Price], 'Date Lookup'[Datum] = a )
    )

    Output

     

     

     

    Best Regards!

    Yolo Zhu

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

     

    • Zoran05's avatar
      Zoran05
      Frequent Visitor
      2 years ago

      Hi Anonymous ,

       

      Many thanks for your fast reply and help :). It's working exactly as I was hoping. 

       

      Thank you so much 🙂

      Best regards

      Zoran