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

[Anonymous]

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.

[v-lili6-msft]

hi, @Anonymous

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

[Anonymous]

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!