Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to Solution.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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' )
)
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
thank you my friend
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
20 | |
19 | |
16 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |