cancel
Showing results for
Did you mean:
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 Month Site Name Account Number Total Usage Total Cost 1/1/2018 Dowtow 29939 50 R\$46.42 4/1/2018 Dowtow 3840 1,600.00 R\$1,086.23 10/1/2017 Dowtow 2 61 3/1/2017 Dowtow 615 32,881.00 R\$18,949.13 1/1/2016 Dowtow 2 3555 27,089.00 R\$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

## 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"))))```

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.
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!