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 would like to achieve the following in a matrix summing order qty totals:
Period 1: 100
Period 1; total of (last 6 periods) including period 1
Period 2: 60
Period 2; total of (last 6 periods)
Periods_vw = a date table.
Orders = order qty table
This formula gives me an error:
Qty last 6 periods = CALCULATE(sum([qty]);FILTER(Periods_VW;DATESBETWEEN(Periods_VW[PeriodDate];DATEADD(Periods_VW[PeriodDate];-6;MONTH);ENDOFMONTH(Periods_VW[PeriodDate]))))
mdxScript(Model) (3,19) Calculation error in measure: Orders[Qty last 6 periods]: a table of multiple values was supplied where a single value was expected
Solved! Go to Solution.
Hi wvanpeel,
Based on your description, you want to sum up qty in last 6month row by row, right?
To achieve your requirement, please create a measure [Qty last 6 periods] and refer to dax formula below:
Qty last 6 periods =
CALCULATE (
SUM ( [qty] ),
DATESBETWEEN (
Periods_VW[PeriodDate],
EDATE ( MAX ( Periods_VW[PeriodDate] ), -6 ),
ENDOFMONTH ( Periods_VW[PeriodDate] )
)
)
The result is like below and you can refer to PBIX:https://www.dropbox.com/s/y1f3n1c5uj6iwhb/for%20wvanpeel.pbix?dl=0
Best Regards,
Jimmy Tao
Hi wvanpeel,
Based on your description, you want to sum up qty in last 6month row by row, right?
To achieve your requirement, please create a measure [Qty last 6 periods] and refer to dax formula below:
Qty last 6 periods =
CALCULATE (
SUM ( [qty] ),
DATESBETWEEN (
Periods_VW[PeriodDate],
EDATE ( MAX ( Periods_VW[PeriodDate] ), -6 ),
ENDOFMONTH ( Periods_VW[PeriodDate] )
)
)
The result is like below and you can refer to PBIX:https://www.dropbox.com/s/y1f3n1c5uj6iwhb/for%20wvanpeel.pbix?dl=0
Best Regards,
Jimmy Tao
Many thx Jimmy this works as a train.
gr
Wim
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |