Forum Discussion

coliveira77's avatar
coliveira77
Frequent Visitor
5 years ago
Solved

The Date table does not relate to another date table

Hello friends from Power Bi, For a week now I have been researching how to include a new table of interest rates and access according to the period of each line. Link to the PBIX files     ...
Need Help
Tutorial Requests
  • coliveira77's avatar
    coliveira77
    5 years ago

    Hello Friend DataInsights thanks for the help, I was able with this formala see the value of the adjustment, then I make another measurement for the final value.

     

    1 Valor Reajuste =
    CALCULATE (
    LASTNONBLANKVALUE (fReajustes[adjustment date] , [sum_reajustes]),
    FILTER (
    ALL ( dCalendar[Date]),
    dCalendar[Date] <= MAX ( dbo[measure_date]
    )
    ))
DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
5 years ago

coliveira77,

 

Do you want a measure or calculated column? Here's the DAX for a measure, with the assumption that if no row exists in fReajustes for a Contract/Date, return the original amount.

 

Adjusted Amount = 
VAR vAmount =
    SUM ( dbo[item_valor_new] )
VAR vContract =
    MAX ( Contracts[Contracts] )
VAR vDate =
    MAX ( dCalendar[Date] )
VAR vRate =
    LOOKUPVALUE (
        fReajustes[adjustment percetual],
        fReajustes[Contracts], vContract,
        fReajustes[adjustment date], vDate
    )
VAR vResult =
    IF ( ISBLANK ( vRate ), vAmount, vAmount * vRate )
RETURN
    vResult

 

 

coliveira77's avatar
coliveira77
Frequent Visitor
5 years ago

Hello, DataInsights Thank you for your help, sorry for the delay, I was trying to solve it with your help.

 

I made a test and still didn't get the expected result, so I made an example of the result I want.

A column calculated according to the "Fat_new" lines of the "dbo" table, multiply with the lines of the "percentage adustment" column
table "fRreaadjustment", respecting the posting dates, not having corresponding value in the rows of the column "percentage adustment" of the table "fReajustes", return the same value of the rows "Fat_new" of the table "dbo".

 

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    5 years ago

    coliveira77,

     

    Try this measure:

     

    Adjusted Amount = 
VAR vAmount =
    SUM ( dbo[Fat_new] )
VAR vContract =
    MAX ( Contracts[Contracts] )
VAR vDate =
    MAX ( dCalendar[Date] )
VAR vMaxRateDate =
    CALCULATE (
        MAX ( fReajustes[adjustment date] ),
        ALL ( fReajustes ),
        fReajustes[adjustment date] <= vDate,
        fReajustes[Contracts] = vContract
    )
VAR vRate =
    LOOKUPVALUE (
        fReajustes[adjustment percetual],
        fReajustes[Contracts], vContract,
        fReajustes[adjustment date], vMaxRateDate
    )
VAR vResult =
    IF ( ISBLANK ( vRate ), vAmount, vAmount * vRate )
RETURN
    vResult

     

     

    • coliveira77's avatar
      coliveira77
      Frequent Visitor
      5 years ago

      Thanks for the help DataInsights, but when I apply it to my file, it comes up with another value, I did some variations without success.

       

      • DataInsights's avatar
        DataInsights
        Icon for Super User rankSuper User
        5 years ago

        coliveira77,

         

        Try breaking it down so you can see each component of the calculation. After RETURN in the measure, put vRate instead of vResult. This will display the rate being used. Do the same with each variable to identify the problem.