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 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!
Need Help
Show and Tell
Tutorial Requests

2 Replies

  • 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,

     

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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,