Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculated table with continous months

Hi,

I have a table with discontinous month data like below:

 

CategoryProductDatePrice
WellnessShampooJan 1 2019100
WellnessShampooFeb 1 2019105
WellnessShampooApr 1 2019120
NutritionEnergy barJan 1 2019200
NutritionEnergy barJuly 1 2019205
NutritionEnergy barMar 1 2020225

 

I want to create a new calculated table with price coming for each month. E.g since March 2019 is not present for Shampoo in Wellness then it should bring previous value 105 of Feb 2019. Basically autofill data from Jan 2019 to March 2021 with previous avaiable values. Similarly for Energy it should fill 200 value for months from Feb 2019 to June 2019 then July 2019 value 205 fro July to Feb 2020. Basically an autofilled table with values from minimum available date in table to maximum available date in table.

 

Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    Anonymous 

    This sort of operation better be done in Power Query. Refer Attached working file below my signature.

    Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.

     

     



1 Reply

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    Anonymous 

    This sort of operation better be done in Power Query. Refer Attached working file below my signature.

    Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.