Max of a sum measure when using slicer, using dax?

[uva0224]

See the pic below:

2100 is the sum of 'total monthly order' over 3 months for a display.

However, some items are not offered in all 3 months, so they don't all 3 rows in the raw data. So the sum caluated 2 month total insead 1350.

Is there any way using DAX to force 2100 on 1350 while keep the flexiablity of the the monthly slicer?

I can solve it in SQL, but not very good in DAX.

Thank you so much!

[v-yuaj-msft · Accepted solution]

Hi @uva0224 ,

Based on your description, I did a test. 

Here is my test table.

Then I created a measure as follows.

sum_date =
SUMX (
    FILTER (
        ALL ( 'Table' ),
        [Date] < DATE ( 2021, 4, 1 )
            && [Date] >= DATE ( 2021, 1, 1 )
    ),
    [Value]
)

Result:

You could add "ALL()" function before the table and specify the scope of date to limit the rows to be calculated.

Is the result what you want?

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please share some sample data and the expected result to have a clear understanding of your question? I can do some tests for you.

You can save your files in some cloud sharing platforms and share the link here. (screenshots are as well)

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,

Yuna

[uva0224]

Help. please! thx