Subtraction of 3 measures with a condition

[Benedictmooray]

Hi all, 

I need some quick help please. I need to change a calculation with the following logic:

All expenses with a status "commit" minus all the expenses with a status "unap" and minus all resource labelled "org" where that "org" expense is within the next 5 months. 

Org expense within the next 5 months is defined as current month + next 5 month e.g if it is Dec currently then it should be December + next 5 months, which will be till May.

Help will be much appreciated.

Thanks

[v-yiruan-msft · Accepted solution]

Hi @Benedictmooray ,

You can create a measure as below to get it:

measure =
VAR _commit =
    CALCULATE (
        SUM ( 'Table'[Expense] ),
        FILTER ( 'Table', 'Table'[status] = "commit" )
    )
VAR _unap =
    CALCULATE (
        SUM ( 'Table'[Expense] ),
        FILTER ( 'Table', 'Table'[status] = "unap" )
    )
VAR _unap =
    CALCULATE (
        SUM ( 'Table'[Expense] ),
        FILTER (
            'Table',
            'Table'[status] = "org"
                && VALUE ( FORMAT ( 'Table'[Date], "YYYYMM" ) )
                    >= VALUE ( FORMAT ( TODAY (), "YYYYMM" ) )
                && VALUE ( FORMAT ( 'Table'[Date], "YYYYMM" ) ) <= EOMONTH ( TODAY (), 5 )
        )
    )
RETURN
    _commit - _unap - _unap

If the above one can't help you get the expected result, please provide some raw data in your tables   (exclude sensitive data) with Text format and your visual settings. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[MFelix]

Hi @Benedictmooray ,

Without any data is difficult to give you a correct answer.

Can you please share a mockup data or sample of your PBIX file with expected result. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.