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

v-yueyunzh-msft's avatar
v-yueyunzh-msft
Community Support
3 years ago

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'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