I´m working in a hard project that I have the following Table structure.
|Service Month||Site Name||Account Number||Total Usage||Total Cost|
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.
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.
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!