unexpected result in some field when do CALCULATE SUM with FILTER ALL and date

[GenK]

I tried below DAX measure but got strange in some field,

----- DAX measure
NewAppNum(acc) =
VAR SelectedDate = SELECTEDVALUE('NewAppNum'[date])
VAR StartMonth = Date(YEAR(SelectedDate),MONTH(SelectedDate),1)
RETURN
CALCULATE(
    SUM('NewAppNum'[NewAppNum]),
    FILTER(
        ALL('NewAppNum'),
        'NewAppNum'[date] >= StartMonty && 'NewAppNum'[date] <= MAX('NewAppNum'[date])
    )
)
----- result
SalesDate | NewAppNum | NewAppNum(acc)
1 | 6 | 6
2 | 11 | 17
3 | 3 | 20
4 | 5 | 105225 <--- why?
5 | 7 | 32
6 | 4 | 36

Don't know why suddenly 105225 apears here. Can anyone help kindly please?
Best Regards

[GenK · Accepted solution]

Thank you FBergamaschi for your advice.

Actually I have resolved it by PowerQuery directly instead of DAX function.

Of course will make chance to improve my DAX skill more continually

Best Regards,

[FBergamaschi]

It is impossible to understand the reason of the issue without sample data, visual settings (grouped columns etc) and data model.

What I can do now is suggest this syntax which is simpler and usually works (and does not correspond to the syntax you have used)

NewAppNum(acc) =
VAR SelectedDate = SELECTEDVALUE('NewAppNum'[date])
VAR StartMonth = Date(YEAR(SelectedDate),MONTH(SelectedDate),1)
RETURN
CALCULATE(
    SUM('NewAppNum'[NewAppNum]),
        'NewAppNum'[date] >= StartMonty && 'NewAppNum'[date] <= MAX('NewAppNum'[date])
)

If it does not work, please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Alternatively, you can share your .pbix via some cloud service and paste the link here. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[GenK · Accepted solution]

Thank you FBergamaschi for your advice.

Actually I have resolved it by PowerQuery directly instead of DAX function.

Of course will make chance to improve my DAX skill more continually

Best Regards,

[GenK]

Thank you Jainesh for replied and sorry about typo.

I tried your advice and got blank in field instead of big number before with below real data,

===== result

1 | 6 | 6

2 | 14 | 20

3 | 7 | 27

4 | 13 | 40

5 | 19 | 59

6 | 13 | 72

7 | 18 | 90

8 | 13 | 103

9 | 11 | 114

10 | 17 |      <-- blank

11 | 16 | 147

12 | 21 | 168

Any idea?

Best regards

[Cookistador]

Hi @GenK 

The following dax code works:

NewAppNum (acc) =

VAR MaxDate = MAX('Table'[date])

RETURN

    CALCULATE(

        SUM('NewAppNum'[NewAppNum]),

        FILTER(

            ALL('NewAppNum'),

            'NewAppNum'[date] <= MaxDate

        )

    )

[GenK]

Thank you Cookistador for replied with code.

I tried it and got all big number in field with below real data,

===== result

1 | 6 | 11789

2 | 14 | 11803

3 | 7 | 11810

4 | 13 | 11823

5 | 19 | 11842

6 | 13 | 11855

7 | 18 | 11873

8 | 13 | 11886

9 | 11 | 11897

10 | 17 | 11914

11 | 16 | 11930

12 | 21 | 11951

Don't know what happened? seem calculation is right on big number?

Best regards

[Cookistador]

Do you have some slicer on the page?

If it is the case, the following measure should return what you need

NewAppNum (acc) =

VAR MaxDate = MAX('Table'[date])

RETURN

    CALCULATE(

        SUM('NewAppNum'[NewAppNum]),

        FILTER(

            ALLSELECTED('NewAppNum'),

            'NewAppNum'[date] <= MaxDate

        )

    )

ALLSELECTED will consider the slicers on your page instead of ALL which ignore the slicers

[GenK · Accepted solution]

Got it. Many thanks for your code.

Best regards.