Forum Discussion

ola1996's avatar
ola1996
Icon for Helper I rankHelper I
5 years ago

YTD or other time intelligence calculation

Hello,

I would like to ask you about specyfic case. My examle is below. I need to have some YTD calculation across multiple years. It means that I want to see one column "Opening balance" it is not standard functionality OPENINGBALANCE in DAX, it need to be sth more. Opening balance for current month  is closing balance for previous month, and closing balance is YTD calculation but I must have this value from the beggining of the world  till now, so  it is not only current year. 

So I prepare closing balance in DAX like 

 CALCULATE(SUM(BANKACCLEDGENTR[Amount_LCY]),DATESYTD('Calendar'[Date]))
and opening 
CALCULATE([closing balance],PREVIOUSMONTH('Calendar'[Date]))
but how to get correct result in the next year. 

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago
    // You should adjust the names of
// the tables and columns to fit
// your model. These measures work
// correctly for any selection of
// periods, not only months.
 
[Closing Balance] =
var LastVisibleDate = MAX( 'Calendar'[Date] )
var Result =
    CALCULATE(
        SUM( T[Amount] ),
        'Calendar'[Date] <= LastVisibleDate,
        // This last line is not needed
        // if 'Calendar' is a date table
        // that's been marked in the model
        // as a proper date table.
        ALL( 'Calendar' )
    )
return
    Result
    
// Opening Balance

[Opening Balance] =
var FirstVisibleDate = MIN( 'Calendar'[Date] )
var DayBeforeFirstVisibleDate =
    FirstVisibleDate - 1
var Result =
    CALCULATE(
        [Closing Balance],
        'Calendar'[Date] = DayBeforeFirstVisibleDate,
        // Same remark as above applies...
        ALL( 'Calendar' )
    )
return
    Result
    • ola1996's avatar
      ola1996
      Icon for Helper I rankHelper I
      5 years ago

      Unfortunatelly, I put this formulas in mine .pbix and not working correctly