DAX Help - Measure to replace numbers

[Anonymous]

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

SUMX(

FILTER(

'Sales',

'Sales'[AS_OF_DATE]

= IF (

ISBLANK ( RELATED ( 'ReplaceDateTable'[GroupNumber]) ), 

'Sales'[AS_OF_DATE], 

RELATED ( 'ReplaceDateTable'[GroupNumber])

)

)

, 'Sales'[Sales_Amount])

However, it is recognizing and replacing only the "good date" with the AS_OF_DATEs sales value (so instead of May 1st being populated with April 24ths data, the measure is populating April 24ths values with April 24)

I am sure I am misunderstanding something. Is such an output possible as a measure?

Let me know if you require more info. Any insights would be helpful.

Thank you!

[v-lid-msft]

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,

[v-lid-msft]

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,