Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Help with dax measure

Hi team,

 

Need help in figuring out the dax to calculate the sum of values for a given merchant with the start and end date.

 

There are sales ouside the start and end date but need to caluclate only cummulative sales for the below 3 promo dates.

 so for merchant 5032917, the value should be $37000.

 

Merchantstartendvalue
503291710/01/202321/01/202315000
50329171/03/202328/03/202312000
50329175/02/202326/02/202310000

 

  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Anonymous 
    'Date' is a disconnected table

    Sales Amount =
SUMX (
    FILTER (
        SaLes,
        Sales[Start] <= MAX ( 'Date'[Date] )
            && Sales[End] >= MAX ( 'Date'[Date] )
    ),
    Sales[Value]
)

    If 'Date' is connected then try

    Sales Amount =
SUMX (
    FILTER (
        ALL ( SaLes ),
        Sales[Start] <= MAX ( 'Date'[Date] )
            && Sales[End] >= MAX ( 'Date'[Date] )
    ),
    Sales[Value]
)

1 Reply

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Anonymous 
    'Date' is a disconnected table

    Sales Amount =
SUMX (
    FILTER (
        SaLes,
        Sales[Start] <= MAX ( 'Date'[Date] )
            && Sales[End] >= MAX ( 'Date'[Date] )
    ),
    Sales[Value]
)

    If 'Date' is connected then try

    Sales Amount =
SUMX (
    FILTER (
        ALL ( SaLes ),
        Sales[Start] <= MAX ( 'Date'[Date] )
            && Sales[End] >= MAX ( 'Date'[Date] )
    ),
    Sales[Value]
)