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.

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.