Forum Discussion

kleedt's avatar
kleedt
Frequent Visitor
3 years ago

Get value for previous day for a string

Hello,

 

I have a column [promotionStatus] indicating for each day ([Date]) whether a product ([EAN])is on promotion or not.

 

I now want to create a measure with [promoStatus] value of the previous day, so I can then flag when the promotion status of an EAN has changed.

 

For this, I have created this measure:

Promo Status previous day = CALCULATE(SELECTEDVALUE('Table'[promotionStatus]), DATEADD('Table'[Date], -1, DAY))
 
This however leads to strange behavior, as PowerBi now seems to skip days where promo status is changing. See example below.

Expected outcome would be for August 7th to have Table[promotionStatus]="on promotion" and 'Promo Status previous day' = "not on promotion"

 

Any idea on what is leading to this beavior and how to fix it?

 

Many thanks!

Need Help

5 Replies

  • Uspace87's avatar
    Uspace87
    Resolver III
    3 years ago

    kleedt 

     

    For me is working:

     

    Was in promo = CALCULATE(
        SELECTEDVALUE(Promo[Promo]),
        DATEADD(Promo[Date],-1,DAY)
    )
     
    I think you need to connect you Dim Date table to the "Date" in the "Table"
      • kleedt's avatar
        kleedt
        Frequent Visitor
        3 years ago

        Uspace87 

        When I use Date and Dim tabel with established connection on date key, no rows are skipped. However, the measure is not giving output as expected (see below). I have not changed the measure, other than referencing Date tabel in DATEADD part of the measure.

         

         

  • kleedt's avatar
    kleedt
    Frequent Visitor
    3 years ago

    Hi Uspace87 ,

     

    thanks for the quick reply.

     

    [Date] is part of the same table as [promotionStatus] and [EAN].

     

    I created a date table and linked it to DIM table, but now the "Promo Status previous day" measure either produces an empty table or, if I create it as calculated column instead of measure, an empty column.

    • Uspace87's avatar
      Uspace87
      Resolver III
      3 years ago

      if you have already a Dim Date Table just link the DateKey to Date into your Fact table.

       

      Check if the dates are correctly formatted in both tables. It should work