Forum Discussion
Need help with time intelligent dax
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 __ResultClosing Balance - Qtr End = [Opening Balance - Year] + [Total Amount]File is attached
Super UserHi 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,
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.