Forum Discussion

visitprateek's avatar
visitprateek
Frequent Visitor
1 year ago
Solved

Need help with time intelligent dax

Hi,

 

I have year to date data, which does not include opening balance. I need to calculate opening balance at the begining of each quarter (which is closing balance of last day of previous year quarter + Opening balance of that year) and then closing balance for that quarter. I have given the example below. Data is quarterly year to date so my dates would be always be same every year. Let me know if any one can help with the DAX to over come this issue.

 

Data I haveData I haveNature of amountThis is the result I want to calculateThis is the result I want to calculate
AmountDateNature of amountOpening Balance for the yearClosing Balance at the end of each quarter
100.0031/12/2021Closing Balance of 2021  
20.0031/03/2022Cumulative Year to date number100120
30.0030/06/2022Cumulative Year to date number100130
50.0030/09/2022Cumulative Year to date number100150
80.0031/12/2022Cumulative Year to date number100180
30.0031/03/2023Cumulative Year to date number180210
40.0030/06/2023Cumulative Year to date number180220
50.0030/09/2023Cumulative Year to date number180230
60.0031/12/2023Cumulative Year to date number180240
40.0031/03/2024Cumulative Year to date number240280
60.0030/06/2024Cumulative Year to date number240300
80.0030/09/2024Cumulative Year to date number240320
90.0031/12/2024Cumulative Year to date number240330

Thank you.

Regards,

Prateek 

 

Need Help
  • Fowmy's avatar
    Fowmy
    1 year ago

    visitprateek 

    I added a dates table as it supports time intelligence functions and it will be useful for your model, you may extend it with additional columns for various uses. 

    Create the following measures:

     

     

    Total Amount = SUM(Data[Amount])
    Opening Balance - Year = 
VAR __CurrDate = MAX('Dates'[Date])
VAR __Result = 
    IF( [Total Amount] <>BLANK(), 
            CALCULATE(
                [Total Amount],
                MONTH( 'Dates'[Date] ) = 12, 
                YEAR('Dates'[Date]) < YEAR( __CurrDate)
            )
    )
RETURN
    __Result
    Closing Balance - Qtr End = 
[Opening Balance - Year] + [Total Amount]

     

     

     

    File is attached

7 Replies

  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    1 year ago

    Hi visitprateek ,

     

    To calculate the Opening Balance at the beginning of each quarter and the Closing Balance at the end of each quarter in Power BI, we need to retrieve the Closing Balance of the last day of the previous year and ensure that the Opening Balance is correctly carried over at the start of each quarter. Since the data consists of quarterly year-to-date values, the Opening Balance for a given year is derived from the Closing Balance of the previous year.

    The following DAX measure calculates the Opening Balance for the Year by retrieving the Closing Balance of the last day of the previous year:

    Opening Balance for the Year = 
VAR PreviousYearClosing =
    CALCULATE(
        MAX('Table'[Amount]),
        'Table'[Date] = MAX('Table'[Date]) - 1,
        'Table'[Nature of amount] = "Closing Balance of 2021"
    )
VAR FirstDateOfYear =
    CALCULATE(
        MIN('Table'[Date]),
        ALLEXCEPT('Table', 'Table'[Year])
    )
RETURN
    IF(
        MAX('Table'[Date]) = FirstDateOfYear,
        PreviousYearClosing,
        BLANK()
    )

    To calculate the Closing Balance at the End of Each Quarter, we simply retrieve the Year-to-Date amount at each quarter-end:

    Closing Balance at End of Quarter = 
MAX('Table'[Amount])

    For the Opening Balance at the Start of Each Quarter, we ensure that it carries over correctly from the previous quarter:

    Opening Balance for Each Quarter =
VAR PreviousQuarterClosing =
    CALCULATE(
        MAX('Table'[Amount]),
        'Table'[Date] = MAX('Table'[Date]) - 1
    )
VAR OpeningBalanceYear =
    CALCULATE(
        MAX('Table'[Amount]),
        'Table'[Date] = MAX('Table'[Date]) - 365
    )
RETURN
    IF(
        MAX('Table'[Date]) = MINX(FILTER('Table', 'Table'[Year] = YEAR(MAX('Table'[Date]))), 'Table'[Date]),
        OpeningBalanceYear,
        PreviousQuarterClosing
    )

    With these measures, the Opening Balance for each year correctly takes the previous year's Closing Balance, while the Opening Balance for each quarter derives from the previous quarter's Closing Balance. The Closing Balance remains the quarterly Year-to-Date value provided in the dataset. This ensures that each quarter starts with the correct cumulative balance while maintaining consistency across years.

     

    Best regards,

    • visitprateek's avatar
      visitprateek
      Frequent Visitor
      1 year ago

      Hi, Thank you for your quick reply. I tried the DAX but still not getting the desired outcome. Also in the first DAX, I dont have any column for Nature of Amount, that was only for the information.

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    1 year ago

    visitprateek 

    I added a dates table as it supports time intelligence functions and it will be useful for your model, you may extend it with additional columns for various uses. 

    Create the following measures:

     

     

    Total Amount = SUM(Data[Amount])
    Opening Balance - Year = 
VAR __CurrDate = MAX('Dates'[Date])
VAR __Result = 
    IF( [Total Amount] <>BLANK(), 
            CALCULATE(
                [Total Amount],
                MONTH( 'Dates'[Date] ) = 12, 
                YEAR('Dates'[Date]) < YEAR( __CurrDate)
            )
    )
RETURN
    __Result
    Closing Balance - Qtr End = 
[Opening Balance - Year] + [Total Amount]

     

     

     

    File is attached

    • visitprateek's avatar
      visitprateek
      Frequent Visitor
      1 year ago

      Thank you. This is super helpful and easy to understand 🙂

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    Hi,

    Share the raw data table.  Also, do you want to write a measure or a calculated column formula?