cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
heitorleaof Frequent Visitor
Frequent Visitor

[Power Query] Fill null cells by specific rules - Using If´s and Time analysys

Good Morning!

 

I´m working in a hard project that I have the following Table structure.

 

Service MonthSite NameAccount NumberTotal UsageTotal Cost
1/1/2018Dowtow2993950R$46.42
4/1/2018Dowtow38401,600.00R$1,086.23
10/1/2017Dowtow 261  
3/1/2017Dowtow61532,881.00R$18,949.13
1/1/2016Dowtow 2355527,089.00R$14,992.92

I need to fill the null cells on columns "Total Usage" and "Total Cost" with the following rules(ALL OF THEM MUST KEEP THE FILTER WITH "ACCOUNT NUMBER COLUMN!)

               

              1 - If the values from the next and previous month is different of null, then the value of null cell must be the                               average between them. 

 

              2 - If step 1 can´t happen (the value of the next or previous month is also null), then the value must be the same                                 of the one year before (fev/2018 (value)  = fev/2017 (value))

              

              3 - If steps 1 and 2 fails (the value of the next or previous month is also null, or the value of one year before is also null),                     then the value must be the average of year.

 

              4 - If all three steps above fails, so the value must be the text "Nothing".

 

One more thing, all the conditions steps above must consider as null all values that are filled by the algorithm, therefore, if the check value for step 1, for example, was filled by the algorithm on the previous or next month, then the steps must consider that value equals null, not the value filled by one of the steps.

 

I hope I´ve explained the problem in a simple way, and I´ll be extremely thankful for any help.

 

 

 

 

 

2 REPLIES 2
Community Support Team
Community Support Team

Re: Fill null cells by specific rules - Using If´s and Time analysys

hi, @heitorleaof

You may try to use PREVIOUSMONTH/ NEXTMONTH/ SAMEPERIODLASTYEAR Function to create a measure like this:

 

Measure = var _PREMONTH =CALCULATE(SUM(Table1[Total Usage]),PREVIOUSMONTH(Table1[Service Month]))
var _NEXTMONTH =CALCULATE(SUM(Table1[Total Usage]),NEXTMONTH(Table1[Service Month]))
var _lyPREMONTH=CALCULATE(SUM(Table1[Total Usage]),SAMEPERIODLASTYEAR(PREVIOUSMONTH(Table1[Service Month])))
var _lyNEXTMONTH=CALCULATE(SUM(Table1[Total Usage]),SAMEPERIODLASTYEAR(NEXTMONTH(Table1[Service Month])))
return
IF(ISBLANK(_PREMONTH*_NEXTMONTH)=FALSE(),AVERAGEX(Table1,_PREMONTH+_NEXTMONTH),
IF(ISBLANK(_PREMONTH)&&ISBLANK(_NEXTMONTH)=FALSE()&&ISBLANK(_lyPREMONTH)=FALSE(),AVERAGEX(Table1,_NEXTMONTH+_lyPREMONTH),IF(ISBLANK(_PREMONTH)=FALSE()&&ISBLANK(_NEXTMONTH)&&ISBLANK(_lyNEXTMONTH),AVERAGEX(Table1,_lyNEXTMONTH+_PREMONTH),
IF(ISBLANK(_PREMONTH*_NEXTMONTH*_lyPREMONTH*_lyNEXTMONTH),CALCULATE(AVERAGE(Table1[Total Usage]),ALLEXCEPT(Table1,Table1[Year],Table1[Account Number])),
"Nothing"))))

If it is not your case, please share sample pbix file or some data sample and expected output. You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

 

 

 

Best Regards,

Lin

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
heitorleaof Frequent Visitor
Frequent Visitor

Re: Fill null cells by specific rules - Using If´s and Time analysys

Hi , @v-lili6-msft...

 

I´m so sorry for not metioned before, but I need this run the code on Power Query, not in DAX.

 

Anyway, thank you so much for the help!