Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Fill missing stock quantity

Hello, stuck in writing measure.

I need to see whole month days and show values for days when values are missing in table.

for example, for HTC 8X, it should be showing 1 for days 7-17 april, then it should show 4 from 19-27.

Appreciate help. thanks!

 

here's pbix link

https://gepowerbi-my.sharepoint.com/:u:/g/personal/info_powerbi_ge/Ea_dwjNJLJBJtM0_UR7ljSwBYpyMavZTP4pRCSD7Atv6rw?e=cI4Q2e 

 

my model is this

 

 

I want to create model like this

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    Inactive the relationship and create measures like below.

    Measure = CALCULATE(SUM(Sheet1[QTY]),FILTER(ALL(Sheet1),Sheet1[Name]=SELECTEDVALUE(Sheet1[Name])&&Sheet1[Date]=SELECTEDVALUE('Table'[Date])))

Measure 2 = if(ISBLANK([Measure]),MAXX(filter(ALLSELECTED('Table'),[Measure]<>BLANK()&&'Table'[Date]<SELECTEDVALUE('Table'[Date])),'Table'[Date]),SELECTEDVALUE('Table'[Date]))

Measure 3 = CALCULATE(SUM(Sheet1[QTY]),FILTER(ALL(Sheet1),Sheet1[Name]=SELECTEDVALUE(Sheet1[Name])&&Sheet1[Date]=[Measure 2]))

     

    Best Regards,

    Jay

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    Inactive the relationship and create measures like below.

    Measure = CALCULATE(SUM(Sheet1[QTY]),FILTER(ALL(Sheet1),Sheet1[Name]=SELECTEDVALUE(Sheet1[Name])&&Sheet1[Date]=SELECTEDVALUE('Table'[Date])))

Measure 2 = if(ISBLANK([Measure]),MAXX(filter(ALLSELECTED('Table'),[Measure]<>BLANK()&&'Table'[Date]<SELECTEDVALUE('Table'[Date])),'Table'[Date]),SELECTEDVALUE('Table'[Date]))

Measure 3 = CALCULATE(SUM(Sheet1[QTY]),FILTER(ALL(Sheet1),Sheet1[Name]=SELECTEDVALUE(Sheet1[Name])&&Sheet1[Date]=[Measure 2]))

     

    Best Regards,

    Jay