@ Help Required

[Anonymous]

Need Help  -

I have a dataset which holds [GroupName] (Indirect Cost, Direct Cost, Purchase, Sales Accounts,Miscellaneous Income,Stock PnL), [MMM_YYY], [Credit] and [Debit].

I want a single DAX Measure or a single Custom Column which will give me the result mentioned below - 

Indirect Cost, Direct Cost and Purchase = [Debit]-[Credit]

Sales Accounts, Miscellaneous Income = [Credit]-[Debit]

Stock PnL = Previous Month ([Credit]-[Debit]) - Current[Credit]-[Debit]

Thanks in advance ...

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Are the [Credit] and [Debit] in the same table?

Which part is not sloved?

Best Regards

Maggie

[Anonymous]

Yes, am not able to crack this -
Stock PnL = Previous Month ([Credit]-[Debit]) - Current[Credit]-[Debit

[v-juanli-msft]

Hi @Anonymous 

Create measures

c-d = SUM('Table'[Credit])-SUM('Table'[Debit])

stock1 = SUMX('Table',[c-d])

stock2 = CALCULATE([stock1],PREVIOUSMONTH('date'[Date]))-[stock1]

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @Anonymous 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?

If not, please feel free to let me know.

Best Regards

Maggie

[Anonymous]

Its not yet resolved, but, its okay, i can close this thread

[MFelix]

Hi @Anonymous ,

What is missing for getting your issue solved? Can you share a sample file and expected results?

[Anonymous]

Stock PnL = Previous Month ([Credit]-[Debit]) - Current[Credit]-[Debit

[MFelix]

Hi @Anonymous ,

Your question is not very clear, however I believe that what you want is based on the group Name you get different results, you need to have a measure similar to this one:

Measure =
SWITCH (
    TRUE ();
    SELECTEDVALUE ( Table[GroupName] )
        IN { "Indirect Cost"; "Direct Cost"; "Purchase" }; SUM ( Table[Debit] ) - SUM ( Table[Credit] );
    SELECTEDVALUE ( Table[GroupName] )
        IN { "Sales Accounts"; "Miscellaneous Income" }; SUM ( Table[Credit] ) - SUM ( Table[Debit] );
    SELECTEDVALUE ( Table[GroupName] ) IN { "Stock PnL" }; CALCULATE (
        SUM ( Table[Credit] ) - SUM ( Table[Debit] );
        DATEADD ( Table[Date]; -1; MONTH )
    )
        - ( SUM ( Table[Credit] ) - SUM ( Table[Debit] ) )
)

Be aware that the calculation for the previous period I made it on a date format since I believe is better for time intelligence you should convert your [MMM_YYY] to a date format end of month for all dates.

This is a measure not a calculated column, but can be adjusted for column.

If you have any question can you please share a sample file and expected results.

Also please see this post regarding How to Get Your Question Answered Quickly (courtesy of @Greg_Deckler) and How to provide sample data in the Power BI Forum (courtesy of @ImkeF).