Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

DAX Help - Measure to replace numbers

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 ...
Need Help
Show and Tell
Tutorial Requests
v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

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,