Get value for previous day for a string

[kleedt]

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!

[kleedt]

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]

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

[Uspace87]

@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"

[Uspace87]

[kleedt]

@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.