Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
User | Count |
---|---|
94 | |
89 | |
79 | |
77 | |
71 |
User | Count |
---|---|
116 | |
107 | |
88 | |
64 | |
63 |