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

[p_rathinavel]

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

[v-yalanwu-msft · Accepted solution]

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.

[p_rathinavel]

@tamerj1