topic Re: if curent month is blank, return last month value in DAX Commands and Tips

if curent month is blank, return last month value

Sofinobi — Sun, 29 Jan 2023 12:16:58 GMT

hello all,

please, i have a measure to calculate the average quantity in stock by month, the problem is when there is no transaction for a product in a month (on sales_tab) it doesn't give me any value, i want to give me the latest value
here is my measure:

AVG Stock Qte Month = var _avgQte= AVERAGEX(Sales_Tab,Sales_Tab[OldLogicalQuantity])

return

if(isblank(_avgQte),

CALCULATE(

AverageX(Values('Date'[Month Year]), _avgQte),filter(all('Date'),'Date'[date] <=max('Date'[date]))), _avgQte)
averageifblanck.pbix

in this image an exemple for the result needed

thank you all

Re: if curent month is blank, return last month value

CNENFRNL — Mon, 30 Jan 2023 00:52:23 GMT

Re: if curent month is blank, return last month value

tamerj1 — Mon, 30 Jan 2023 12:33:01 GMT

Hi Sofinobi
If the added values shall be counted in the total, you may try this as per attached sample file.

Average Filldown = AVERAGEX ( VALUES ( 'Date'[YearMonth Number] ), VAR CurrentMonth = 'Date'[YearMonth Number] VAR LastDateWithData = MAXX ( FILTER ( CALCULATETABLE ( VALUES ( Sales_Tab[CreationDate] ), ALL ( 'Date' ) ), FORMAT ( Sales_Tab[CreationDate], "YYYYMM" ) <= CurrentMonth ), Sales_Tab[CreationDate] ) VAR LastMonthWithData = FORMAT ( LastDateWithData, "YYYYMM" ) RETURN CALCULATE ( AVERAGE ( Sales_Tab[OldLogicalQuantity] ), 'Date'[YearMonth Number] = LastMonthWithData, ALL ( 'Date' ) ) )

Re: if curent month is blank, return last month value

Sofinobi — Wed, 01 Feb 2023 10:41:15 GMT

thank you my friend