Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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!
2 REPLIES 2
v-lid-msft
Community Support
Community Support

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,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-lid-msft
Community Support
Community Support

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 ()
                    )
            )
        )
)

 

1.jpg

 


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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.