Forum Discussion

hokitkan's avatar
hokitkan
Frequent Visitor
9 years ago
Solved

Generate missing data at certain time points

Hello, I'm new to power BI and now I'm facing a problem with missing data. I've got a table recorded the growth rate of different kinds of virus as follow:   What I want to do is to create a...
General Comment
Need Help
Tips and Tricks
Tutorial Requests
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi hokitkan,

     

    You can create a table below:

     

    Table 2 = UNION(ADDCOLUMNS(EXCEPT(CROSSJOIN(SUMMARIZE(Table1,[Colum1],[Colum2]),VALUES('Table'[Column4])),SELECTCOLUMNS(Table1,"Colum1",[Colum1],"Colum2",[Colum2],"Column4",[Column4])),"Column3",0),SELECTCOLUMNS(Table1,"Colum1",[Colum1],"Colum2",[Colum2],"Column4",[Column4],"Column3",[Column3]))

     

    Then create a calculated column below:

     

    Amount = if([Column3]<>0,[Column3], MAXX(FILTER(ALL('Table 2'),[Colum1]=EARLIER([Colum1])&&[Colum2]=EARLIER('Table 2'[Colum2])&&[Column4]<EARLIER([Column4])&&[Column3]<>0),[Column3]))+0

     

     

     

    Best Regards,
    Qiuyun Yu

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
9 years ago

Hi hokitkan,

 

You can create a table below:

 

Table 2 = UNION(ADDCOLUMNS(EXCEPT(CROSSJOIN(SUMMARIZE(Table1,[Colum1],[Colum2]),VALUES('Table'[Column4])),SELECTCOLUMNS(Table1,"Colum1",[Colum1],"Colum2",[Colum2],"Column4",[Column4])),"Column3",0),SELECTCOLUMNS(Table1,"Colum1",[Colum1],"Colum2",[Colum2],"Column4",[Column4],"Column3",[Column3]))

 

Then create a calculated column below:

 

Amount = if([Column3]<>0,[Column3], MAXX(FILTER(ALL('Table 2'),[Colum1]=EARLIER([Colum1])&&[Colum2]=EARLIER('Table 2'[Colum2])&&[Column4]<EARLIER([Column4])&&[Column3]<>0),[Column3]))+0

 

 

 

Best Regards,
Qiuyun Yu

  • hokitkan's avatar
    hokitkan
    Frequent Visitor
    9 years ago

    It works perfectly! Thanks for your efforts!