issue with report

[moronilms]

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?

[Anonymous · Accepted solution]

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.

[Rupak_bi]

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

[Kedar_Pande]

@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]

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

[Angith_Nair]

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].

[moronilms]

Não funcionou