Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Missing values per week - return latest active value

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!

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

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

    And  

     

    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. 

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

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

    And  

     

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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