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

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

moronilms's avatar
moronilms
Frequent Visitor
1 year ago

Não funcionou