Forum Discussion
ola1996
Helper I
5 years agoYTD 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 ...
Anonymous
5 years agoNot applicable
// 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
Resultola1996
Helper I
5 years agoUnfortunatelly, I put this formulas in mine .pbix and not working correctly