topic YTD or other time intelligence calculation in DAX Commands and Tips

YTD or other time intelligence calculation

ola1996 — Mon, 26 Apr 2021 18:06:25 GMT

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.

Re: YTD or other time intelligence calculation

Anonymous — Mon, 26 Apr 2021 20:54:37 GMT

// 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

Re: YTD or other time intelligence calculation

ola1996 — Fri, 30 Apr 2021 07:56:57 GMT

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