DAX cumulative date AND location, second filter isn't working

[IAM]

Hi all,

I figured out a formula to calculate the cumulative of new and closed orders. 

It seems to work, but when I calculate myself, it is off.

So the month of January 755 cumulative is correct, but the month of February should be 1327, not 1426

This happens because the filter for the terminal isn't working. Can anyone tell me what I need to change in my formula?

[amitchandak]

@IAM , the Number is correct as per your formula, Can share the logic ?

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[IAM]

Sadly I cannot share this..

The problem is the red part of the formula. It isn't using this part of the filter.

It should filter out only the terminal as the name of the terminal that is on that row.

Cumulative =

CALCULATE(

SUM('Calculate1'[NewWorkorders] ),

FILTER (ALL ('Calculate1'),

'Calculate1'[First day] <= EARLIER('Calculate1'[First day])

&& 'Calculate1'[Terminal] = Calculate1[Terminal]))

-

CALCULATE(

SUM('Calculate1'[closedWorkorders] ),

FILTER ( ALL ('Calculate1'),

'Calculate1'[First day] <= EARLIER('Calculate1'[First day])

&& 'Calculate1'[Terminal] = Calculate1[Terminal]))

[v-yiruan-msft · Accepted solution]

Hi @IAM ,

Please update the formula of measure [Cumulative] as below and check whether that can return the correct result...

Cumulative =
CALCULATE (
    SUM ( 'Calculate1'[NewWorkorders] ),
    FILTER (
        ALL ( 'Calculate1' ),
        'Calculate1'[First day] <= EARLIER ( 'Calculate1'[First day] )
            && 'Calculate1'[Terminal] = EARLIER ( Calculate1[Terminal] )
    )
)
    - CALCULATE (
        SUM ( 'Calculate1'[closedWorkorders] ),
        FILTER (
            ALL ( 'Calculate1' ),
            'Calculate1'[First day] <= EARLIER ( 'Calculate1'[First day] )
                && 'Calculate1'[Terminal] = EARLIER ( Calculate1[Terminal] )
        )
    )

If the above one can't help you get the desired result, please provide some sample data in Calculate1 table (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

How to upload PBI in Community

Best Regards