Help on DAX: Calculate, All, Selectedvalue, Userelationship

[Vietle111]

Dear community,

I create 2 period columns for quarter comparison over years for my accounting report. Ideally, every entry record has a pair of both Debit & Credit. So when I select an acount in Debit, then another table will show Credit - (to find its counterparty: I use DAX: <>selectedvalue)

This is my current model

I have these formulas:

This works for me: to find counterparty of The First Period (Period 1)

Counterparty =

VAR ExcludePL=SELECTEDVALUE('0-PL List'[PL Account])

RETURN

CALCULATE(sum('2. PL Entry'[Base Amount]),FILTER('2. PL Entry','2. PL Entry'[Account Code]<>ExcludePL),

TREATAS(VALUES('0-PL Period'[Period]),'2. PL Entry'[Period]))

This doesn't work: I am trying to find counterparty for Second Period (period 2)

Counterparty Pre =

VAR ExcludePL=SELECTEDVALUE('0-PL List'[PL Account])

RETURN

CALCULATE(SUM('2. PL Entry'[Base Amount]),ALL('0-PL Period'),USERELATIONSHIP('0-PL Period Compare'[Period],'2. PL Entry'[Period]),'2. PL Entry'[Account Code]<>ExcludePL)

 I tried different ways to write DAX for second period: result can be 0 or dupicate each value of subtotal. Please help me to review above formula.

Thanks.

 

[Vietle111]

Hi Wu,

I created a copy of data, so please help me to revise Dax fomular Counterparty of Previous period. Thanks
https://www.dropbox.com/t/o3pPuqpTKGBF4uR5 

[Anonymous]

Hi @Vietle111 ,

Based on the description, please try the following DAX formula:

Counterparty Pre =
VAR ExcludePL = SELECTEDVALUE('0-PL List'[PL Account])
RETURN
CALCULATE(
    SUM('2. PL Entry'[Base Amount]),
    FILTER(
        ALL('2. PL Entry'),
        '2. PL Entry'[Account Code] <> ExcludePL
    ),
    USERELATIONSHIP('0-PL Period Compare'[Period], '2. PL Entry'[Period])
)

View the following documents to learn about dax function.

ALL function (DAX) - DAX | Microsoft Learn

USERELATIONSHIP function (DAX) - DAX | Microsoft Learn

If the above ones can’t help you get it working, could you please provide more raw data(exclude sensitive data) with Text format to make a deep troubleshooting? It would be helpful to find out the solution.

You can refer the following links to share the information:

How to provide sample data in the Power BI Forum

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vietle111]

Hi Wu,

I tried the fomular you provided but it returns blank instead. Figuring out the way to provide dataset after removing all sensitive data.
I'll back to you soon.

Thanks.