Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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.
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.
User | Count |
---|---|
57 | |
48 | |
18 | |
16 | |
15 |
User | Count |
---|---|
116 | |
42 | |
41 | |
28 | |
21 |