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

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 🙂