Missing values per week - return latest active value

[Anonymous]

Guys im running into a problem at work here and through hours of googling and video-watching I have yet to find the answer.

Simply put:

I have weeks with a certain price for an item.

This is only showing in the data set if there is a price change.

If there is no price change then it doesnt show in the data.

Example:

Week 1 - 1,83 EUR

Week 3 - 1,85 EUR

Week 6 - 1,86 EUR

so week 2, 4, 5 are empty.

However I want to create a measure where it will show the latest price for that week.

Week 1 - 1,83 EUR

Week 2 - 1,83 EUR

Week 3 - 1,85 EUR

Week 4 - 1,85 EUR

Week 5 - 1,85 EUR

Week 6 - 1,86 EUR

The prices are also on date level but I want to set a price per week rather than day.

Any help is much appreciated here!

[Anonymous · Accepted solution]

Hi @Anonymous 

If you have a large number of weeks, you may need to use dax to build a separate week table.

And  amitchandak 's measure works well if you build relationships between two tables.

You can try my measure if you don't want to build relationships between two tables.

Firstly, we add a WeekNum column in Price Table.

WeekNum = SUBSTITUTE('Price'[Week],"Week ","")

Change the column type from text to whole number.

Then build Allweek Table.

AllWeek = ADDCOLUMNS(GENERATESERIES(MIN('Price'[WeekNum]),MAX('Price'[WeekNum]),1),"Week","Week"&" "&[Value])

Measurez:

Price = 
VAR _P1 =
    CALCULATE (
        MAX ( 'Price'[Price] ),
        FILTER ( 'Price', 'Price'[Week] = MAX ( AllWeek[Week] ) )
    )
VAR _MaxNum =
    MAXX (
        FILTER ( ALL ( 'Price' ), 'Price'[WeekNum] <= MAX ( AllWeek[Value] ) ),
        'Price'[WeekNum]
    )
VAR _P2 =
    CALCULATE (
        MAX ( 'Price'[Price] ),
        FILTER ( 'Price', 'Price'[WeekNum] = _MaxNum )
    )
RETURN
    IF ( _P1 = BLANK (), _P2, _P1 )

Result:

You can download the pbix file from this link: Missing values per week - return latest active value

Best Regards,

Rico Zhou

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

[Anonymous]

Hi @Anonymous,

thanks for the help. I have been able to rebuild your example.

Now There is an additional level of complexity in this.

The data is also on item and customer level.

How do I work this into your example?

Example data:

Week - item - customer - price

Week 1 - item a - cust 1 - 1,83

Week 1 - item a - cust 2 - 1,85

Week 2 - item b - cust 1 - 2,50

Week 2 - item a - cust 2 - 1,86

Week 3 - item a - cust 1 - 1,85

Week 5 - item b - cust 1 - 2,60

Week 6 - item a - cust 1 - 1,86

etc.

From this I want to be able to look at the price per customer and calculate (using the cost price per customer) the margin of each item per week.

I created a file with an example in.

example file 

[amitchandak]

@Anonymous , Create a separate week tabe with all weeks and try a formula like

calculate(lastnonblankvalue(Week[Week], max(Table[Value])), filter(allselected(Week), Week[Week] <=Max(Week[week])))

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.