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 have a Calendar Table, which include as a column the First day of the month, and a Sales Order Table, which is related to it by the Sales Order Created Date. The Sales Order Table is also related to a Customers table, via a Customer Account field
I have created a measure in the Calendar table, to calculate cumulative order values from start of month, each month
This works fine, but if select certain customers from a slicers, this does not impact the result. I would have expected the slicer selection to filter the Sales Order table, and the dates filters in the measure to be applied on top
Month Cumulative Order Intake =
CALCULATE(SUMX('Sales Orders Totals','Sales Orders Totals'[Total Ordered Value])
,FILTER('Sales Orders Totals','Sales Orders Totals'[Created Date]>='Calendar'[Month Start]
&& 'Sales Orders Totals'[Created Date]<='Calendar'[Date]))
Solved! Go to Solution.
Hi Pete
Many thanks, that little hint actually help a lot... I duplicated the Sales Order table as unrelated to Calendar, used similar, and it now behave as expected
@LAURENTW
Can you see if this works?
Month Cumulative Order Intake =
CALCULATE (
SUM( 'Sales Orders Totals'[Total Ordered Value] ),
FILTER (
ALL('Sales Orders Totals'[Created Date]),
'Sales Orders Totals'[Created Date] >= max('Calendar'[Month Start])
&& 'Sales Orders Totals'[Created Date] <= max('Calendar'[Date])
)
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thank you for the help. That did not work, but it looks like the issue was with having relationship between Calendar table and Order table, and at same time filtering. Removed relationship and good now
Hi @LAURENTW ,
Can you post a picture of your model please?
At first glance, it looks like a relationship setup issue but want to be sure.
Thanks,
Pete
Proud to be a Datanaut!
Hi Pete
Many thanks, that little hint actually help a lot... I duplicated the Sales Order table as unrelated to Calendar, used similar, and it now behave as expected
Great news, happy to help. Also happy to receive credit where due 😉
Pete
Proud to be a Datanaut!
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |