Forum Discussion

AxelKAp's avatar
AxelKAp
Helper I
3 years ago
Solved

DAX Formula

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 Inv...
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    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

AxelKAp's avatar
AxelKAp
Helper I
3 years ago

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's avatar
v-yueyunzh-msft
Community Support
3 years ago

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