Forum Discussion

IAM's avatar
IAM
Icon for Helper III rankHelper III
4 years ago
Solved

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

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 cor...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

IAM's avatar
IAM
Icon for Helper III rankHelper III
4 years ago

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]))
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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