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,
I am trying to create a measure that will replace a particular sales order for a particular day (aggregated to a group number) with a sales amount of a different day. The model contains a fact table and the helper table which contains a group number, bad date, and good date. The group number carries the relationship (1 to *).
For example: I wish to aggregate all the sales by Group number "A12" and then replace A12's total sales from May 1st with the total sales from April 24th.
I have been trying to utilize
Hi @Anonymous ,
How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?
Best regards,
Hi @Anonymous ,
We can try to use the following measure to meet your requirement:
Measure =
var t = ALLSELECTED(Sales)
return
SUMX (
DISTINCT ( 'Sales'[AS_OF_DATE] ),
VAR currentDate = 'Sales'[AS_OF_DATE]
RETURN
CALCULATE (
SUM ( 'Sales'[Sales_Amount] ),
FILTER (
t,
VAR BadDate =
CALCULATETABLE (
DISTINCT ( 'ReplaceDateTable'[bad date] ),
RELATEDTABLE ( ReplaceDateTable )
)
VAR GoodDate =
CALCULATETABLE (
DISTINCT ( 'ReplaceDateTable'[good date] ),
RELATEDTABLE ( ReplaceDateTable )
)
RETURN
IF (
'Sales'[AS_OF_DATE] IN GoodDate
&& currentDate IN BadDate
|| ( NOT ( currentDate IN BadDate )
&& currentDate = 'Sales'[AS_OF_DATE] ),
TRUE (),
FALSE ()
)
)
)
)
If it doesn't meet your requirement, Could you please show the exact expected result based on the tables that we have shared?
By the way, PBIX file as attached.
Best regards,
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |