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.
I want to take Monthly Average of few numbers from a certain date range
Both the figures [ Date ] & [ Qty] are available in single table.
I am trying to use following formula :
Average Demand = AVERAGEX(VALUES[Table1[Date].[Month]),CALCULATE(SUM(Table1[QTY],DATESBETWEEN[Table1[Date], Start Date , End Date))
Output I am getting is simply 'Summation' of Qty , not the 'Monthly Average' as desired.
If I remove .[Month] , it gives me certain average but it is based on 'Daily Average' - not 'Monthly' -
Can someone please help in here?
Solved! Go to Solution.
hi @ANHAMINE
you need a dedicated month column to average on, like
Month = FORMAT([Date], "YYYYMM")
then rewrite the measure like:
Average Demand = AVERAGEX(VALUES[Table1[Month]),CALCULATE(SUM(Table1[QTY],DATESBETWEEN[Table1[Date], Start Date , End Date))
hi @ANHAMINE
you need a dedicated month column to average on, like
Month = FORMAT([Date], "YYYYMM")
then rewrite the measure like:
Average Demand = AVERAGEX(VALUES[Table1[Month]),CALCULATE(SUM(Table1[QTY],DATESBETWEEN[Table1[Date], Start Date , End Date))
It worked ! You are simply magical 😊
Thank you for all your help !
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 |
---|---|
56 | |
27 | |
24 | |
14 | |
9 |
User | Count |
---|---|
77 | |
61 | |
47 | |
17 | |
12 |