Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Daily Closing Balance

  I am trying to visualise bank balance trend, i am facing error while trying to create powerbi dax for this. My data is in the following table format: Date,closing_balance,bank_name 2023-05-25 ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

     

    I suggest you to create a calendar table and inactive the relationship between two tables.

    Measure:

    Balance Measure = 
VAR _Date =
    CALCULATE (
        MAX ( 'Calendar'[Date] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
                && CALCULATE (
                    SUM ( Bank[closing_balance] ),
                    USERELATIONSHIP ( Bank[transaction_date], 'Calendar'[Date] )
                )
                    <> BLANK ()
        )
    )
RETURN
    CALCULATE (
        SUM ( Bank[closing_balance] ),
        FILTER ( Bank, Bank[transaction_date] = _Date )
    )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

Anonymous , You should use date table joined with date of your table in visual and meausre

 

 

example

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))

 

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.