DAX Formula

[AxelKAp]

Hi everyone, 

I need your help to create a complicated DAX Formula. I would like to calculate fees each month with the Invoice Date), but for one Purchase Order Number I can have more than one Invoice Number (as the example below).
To calculate the fees, we have this rule:
- if the PO Amount Total of the Purchase Order Number is <800€ we have 20€ of fees for the Purchase Order Number
- else if the PO Amout Total is >=800€ we calculate the fees like that -> Invoice Amount in € * 0.025

Here, I have some difficulties to calculate the first scenario (if the PO Amount Total is <800€). Because sometimes we have more than one invoice number for a Purchase Order Number. Sometimes we have as the example below 3 invoices for 1 PO and all the 3 invoices have the same Invoice Date and the last thing it's sometimes for 1 PO we have 1 invoice in September 2022 and another in November 2022 for the same PO for example.

All the fields are available in the same table (Invoice Table) except the PO Amount Total (in the PO Table). 

In the example below I have 3 invoices for 1 PO and here the PO Amount Total is 502,93€ which is <800€ so we have to only 1 row with 20€ of fees and the 2 others we need to have 0€. But each time I have 20€ of fees..

So as you you can see, it's very difficult to build a DAX formula that can answer to my need. That's why I need you help !

Thanks in advance for your help 🙂 

[v-yueyunzh-msft]

Hi , @AxelKAp 

According to your description, you want to exclude the value of the same date, and determine whether the sum of each month is greater than or equal to or less than 800 under the premise of different dates to find different logical values.

Here are the steps you can refer to :
(1)This is my test data:

(2)We can click "New Column" to create two calculated columns:

Month Total = var _curPO= [PO Number] 
var _cur_year_month = YEAR([Invoice AL Date])*100 + MONTH([Invoice AL Date])
var _t = FILTER('Table', 'Table'[PO Number] =_curPO && YEAR('Table'[Invoice AL Date])*100+MONTH('Table'[Invoice AL Date]) = _cur_year_month)
var _t2 = GROUPBY(_t , [Invoice AL Date] , "Amount" , MAXX( CURRENTGROUP() , [PO Amount Total]))
return
SUMX(_t2,[Amount])

Fees in = var _amount_month = [Month Total]
var _PO = [PO Number]
var _date = [Invoice AL Date]
var _AL = [Invoice Number AL]
var _amount = [PO Amount Total]
var _t=  FILTER('Table','Table'[PO Number] = _PO && 'Table'[Invoice AL Date] = _date)
var _show_AL = MINX( _t , [Invoice Number AL])
return
IF(_amount_month<800 && _AL = _show_AL , 20 , IF(_amount_month<800 && _AL <> _show_AL , 0 , _amount * 0.025))

(3)Then we can meet your need , the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[AxelKAp]

Hello @v-yueyunzh-msft, 

First of all thank you for your help !
So I tested your solution but I have changed something in your solution, because here I don't need the calculated column Month Total because the rule to calculate the fees is only made with the PO Amount Total. I have this now: 

Fees Ultime Version =

var _PO = 'Fees SDI (2)'[PO_NUMBER]

var _Date = 'Fees SDI (2)'[INVOICE_ACCOUNTING_DATE]

var _AL = 'Fees SDI (2)'[INVOICE_NO]

var _Amount = [PO Amount Total]

var _t = FILTER('Fees SDI (2)', 'Fees SDI (2)'[PO_NUMBER] = _PO && 'Fees SDI (2)'[INVOICE_ACCOUNTING_DATE] = _Date)

var _show_AL = MINX(_t, 'Fees SDI (2)'[INVOICE_NO])

return

IF([PO Amount Total] < 800 && _AL = _show_AL, 20, IF([PO Amount Total]<800 && _AL <> _show_AL, 0, 'Fees SDI (2)'[Invoice_Accounting_EUR]*0.025))

It's working for the most of  the fees but sometimes I have it's not working. I 'm sharing with example when it's not working.

You can see for the PO 5110622278 I have 1 line with 0€ (OK) but i have 2 lines with 20€ of fees but I need 1 line with 20€.

Thanks again for your support 😉 

[v-yueyunzh-msft · Accepted solution]

Hi , @AxelKAp 

Thanks for your quick response and sorry for the delay response due to the different work time zone.

For this issue , i ignore one judgement in dax , you can try to use this dax then we can get the right result:

Fees in =
VAR _amount_month = [Month Total]
VAR _PO = [PO Number]
VAR _date = [Invoice AL Date]
VAR _AL = [Invoice Number AL]
VAR _amount = [PO Amount Total]
VAR _t =
    FILTER (
        'Table',
        'Table'[PO Number] = _PO
            && YEAR ( 'Table'[Invoice AL Date] ) = YEAR ( _date )
            && MONTH ( 'Table'[Invoice AL Date] ) = MONTH ( _date )
    )
VAR _show_AL =
    MINX ( _t, [Invoice Number AL] )
RETURN
    IF (
        _amount_month < 800
            && _AL = _show_AL,
        20,
        IF ( _amount_month < 800 && _AL <> _show_AL, 0, _amount * 0.025 )
    )

We just need to add the "YEAR('Table'[Invoice AL Date]) = YEAR( date) && MONTH('Table'[Invoice AL Date]) = MONTH( date)" logic judgement in the "_t" parameter , the result is as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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