topic Re: DAX to Add missing date (end of month) and assign last non zero / blank value in DAX Commands and Tips

DAX to Add missing date (end of month) and assign last non zero / blank value

p_rathinavel — Tue, 31 May 2022 14:29:43 GMT

Hi All,

I’m seeking help with some tricks with DAX, I’m trying to calculate the price of an item bought each month. But the item is not bought during few months in a year. For the missing months I want to add the rows with the last date of the month and take the price of the last non blank value.

Please refer the example below

Thanks for the help in advance

NOTE as i have million of different items for the many year it is difficult to figure out an idea please help

Re: DAX to Add missing date (end of month) and assign last non zero / blank value

p_rathinavel — Tue, 31 May 2022 14:31:23 GMT

tamerj1

Re: DAX to Add missing date (end of month) and assign last non zero / blank value

v-yalanwu-msft — Fri, 03 Jun 2022 06:18:34 GMT

Hi, p_rathinavel ;

You need add another date table expand all date in every month.

Date = SUMMARIZE( ADDCOLUMNS( CALENDAR(MIN('Table1'[Date]),MAX('Table1'[Date])),"Date end",EOMONTH([Date],0),"Item",MAX('Table1'[Item])),[Date end],[Item])

Then create a measure.

Measure = var _date=CALCULATE(MIN('Table1'[End of Month]),FILTER('Table1',[End of Month]>=MAX('Date'[Date end]))) return IF(MAX('Date'[Date end]) in VALUES('Table1'[End of Month]), CALCULATE(SUM('Table1'[Price]),FILTER('Table1',[End of Month]=MAX('Date'[Date end]))), CALCULATE(SUM('Table1'[Price]),FILTER('Table1',[End of Month]=_date)))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.