Forum Discussion

moronilms's avatar
moronilms
Frequent Visitor
1 year ago
Solved

issue with report

I have a table with the columns company_id, account (bank account), date and balance. The customer wants to view it in a table view and can filter by date and company. When filtering a date, it will be able to view the balance records for that date (if any), if there are no records for that date, it should show the last record for each account. It is important to know that each company has certain accounts. I can't do this, can anyone help?

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for the replies from Angith_Nair, Rupak_bi and Kedar_Pande.

     

    Hi moronilms,

     

    Based on your description I created simple data:

     

    Please try the following steps:

    1.Create a new table:

    Newtable = CROSSJOIN('company','date')

     

    2.Create a new column:

    Balance = LOOKUPVALUE('Table'[balance],'Table'[company_id],'Newtable'[company_id],'Table'[date],'Newtable'[Date])

     

    3.The relationships like this:

     

    4.Create a new measure:

    Newbalance = 
VAR _Previousdate = CALCULATE(MAX('Newtable'[Date]),FILTER(ALLEXCEPT('Newtable','Newtable'[company_id]),NOT('Newtable'[Balance]=BLANK())))
VAR _interval=DATEDIFF(_Previousdate,MAX('Newtable'[Date]),DAY)
 return
IF(MAX('Newtable'[Balance])=BLANK(),CALCULATE(MAX('Newtable'[Balance]),FILTER(ALLEXCEPT('Newtable','Newtable'[company_id]),'Newtable'[Date]=MAX('Newtable'[Date])-_interval)),MAX('Newtable'[Balance]))

     

    5.The results are as follows:

     

    Best Regards,
    Zhu

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

6 Replies

  • Angith_Nair's avatar
    Angith_Nair
    Continued Contributor
    1 year ago

    Hi moronilms 

    To achieve this, you can use a DAX measure that dynamically retrieves the last balance record for each account when there are no records for the selected date. Please find the steps below:

    1. Create a Date Filtering Measure: This measure will determine whether there are any balance records for the selected date for each account and, if not, retrieve the last available record.

    2. Implement a Filtered Last Balance Measure: This measure will calculate the balance for the selected date. If no balance is available for that date, it will retrieve the last recorded balance prior to the selected date.

    Use the below DAX:

    BalanceFiltered = 
VAR SelectedDate = MAX('DateTable'[Date])  // Assuming a Date Table for date filtering
VAR LastDateWithBalance = 
    CALCULATE(
        MAX('BalanceTable'[Date]),
        FILTER(
            'BalanceTable',
            'BalanceTable'[Date] <= SelectedDate 
                && 'BalanceTable'[company_id] = SELECTEDVALUE('BalanceTable'[company_id])
                && 'BalanceTable'[account] = SELECTEDVALUE('BalanceTable'[account])
        )
    )
RETURN
    CALCULATE(
        SUM('BalanceTable'[balance]),
        'BalanceTable'[Date] = LastDateWithBalance
    )

    Note:

    > Make sure you have a separate Date Table linked to BalanceTable[Date] to allow proper date filtering in Power BI.

    > Display this measure in a table visualization with columns company_id, account, and BalanceFiltered, and add filters for company_id and DateTable[Date].

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    moronilms 

    Create a Measure for Latest Balance by Date

    Latest Balance = 
    VAR SelectedDate = MAX('DateTable'[Date]) 
    VAR LatestBalance = 
    CALCULATE(
    MAX('Balances'[balance]), 
    FILTER(
    'Balances', 
    'Balances'[date] <= SelectedDate && 
    'Balances'[company_id] = SELECTEDVALUE('Balances'[company_id]) && 
    'Balances'[account] = EARLIER('Balances'[account]) 
    )
    )
    RETURN
    LatestBalance

    In the table visual:

    Add columns for company_id, account, and the Latest Balance measure.
    Ensure the slicers for Date and Company are applied to this visual.

     

    💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn

     

    • moronilms's avatar
      moronilms
      Frequent Visitor
      1 year ago

      EARLIER/EARLIEST faz referência a um contexto de linha anterior que não existe.

  • Rupak_bi's avatar
    Rupak_bi
    Super User
    1 year ago

    please share sample data to better understand the data structure. also shared the desired output.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for the replies from Angith_Nair, Rupak_bi and Kedar_Pande.

     

    Hi moronilms,

     

    Based on your description I created simple data:

     

    Please try the following steps:

    1.Create a new table:

    Newtable = CROSSJOIN('company','date')

     

    2.Create a new column:

    Balance = LOOKUPVALUE('Table'[balance],'Table'[company_id],'Newtable'[company_id],'Table'[date],'Newtable'[Date])

     

    3.The relationships like this:

     

    4.Create a new measure:

    Newbalance = 
VAR _Previousdate = CALCULATE(MAX('Newtable'[Date]),FILTER(ALLEXCEPT('Newtable','Newtable'[company_id]),NOT('Newtable'[Balance]=BLANK())))
VAR _interval=DATEDIFF(_Previousdate,MAX('Newtable'[Date]),DAY)
 return
IF(MAX('Newtable'[Balance])=BLANK(),CALCULATE(MAX('Newtable'[Balance]),FILTER(ALLEXCEPT('Newtable','Newtable'[company_id]),'Newtable'[Date]=MAX('Newtable'[Date])-_interval)),MAX('Newtable'[Balance]))

     

    5.The results are as follows:

     

    Best Regards,
    Zhu

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.