Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
4 years ago

Rebuild overdue debt balance

Good morning people, please need your help with the following problem

1-I have the following excel table where I would like to be able to replicate it in power Bi according to the selected date filter

jtur_0-1654622301252.png

Cut-off date = date selected in the filter (calendar date)

2-The objective is to be able to reconstruct the debt of the clients, according to the date selected in the calendar filter

the logic that is used in the excel file is as follows

AE BALANCE column =SI(B30<$C$1,SI(K30<$C$1,0; F30);0)

AF STATUS column =IF(AE30>0,SI(J30<$C$1;" EXPIRED";" TO BE OVERCOME");0). According to the cut-off date, these values are classified as Expired, by Vencer or 0.

My query is if I can apply this logic in power bi from the option reports through measures and variables or from the data option. From the data option can I link the calendar date selected in the report filter?

I would appreciate your help please.

Thanks a lot.

3 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi  Syndicate_Admin jtur 

    Thanks for reaching out to us.

    I copied your formula into excel but don't know what it's going to calculate

    Could you please explain what are you trying to calculate?

     

     

    Best Regards,

    Community Support Team _Tang

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

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Icon for Administrator rankAdministrator
      4 years ago

      Hello, thank you very much for the response. The problem is that there were hidden columns, so you are not turning out the formula.

      According to the Excel file that you put together, the formula would be the following.

      =SI(B3<$B$1;SI(I30<$B$1;0; F30);0)

      =SI(j30>0,SI(H30<$B$1;" EXPIRED";" TO BE OVERCOME");0)

      Keep in mind that the selected date $B$1 would be the selected calendar date filter

      Thanks a lot!!

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Icon for Administrator rankAdministrator
      4 years ago

      Hello, thank you very much for the reply. The problem is that there were hidden columns, so you are not turning out the formula.

      According to the Excel file that you put together, the formula would be the following.

      =SI(B3<$B$1;SI(I30<$B$1;0; F30);0)

      =SI(j30>0,SI(H30<$B$1;" EXPIRED";" TO BE OVERCOME");0)

      Keep in mind that the selected date $B$1 would be the selected calendar date filter

      Thanks a lot!!