last year december balance and end of previous quarter balance

[Anonymous]

Dear all

I had a measure to calculate balance at the end of year(dec) and balance at the end of each quarter and month as below which is working fine

Selected ARBalLegal = SWITCH([SelectedPeriod],

"Month", CALCULATE(SUM(Aging_table[TotalAmount]),Aging_table[ClassLegal]="L", Aging_table[TotalAmount]>0,),

"Quarter", CALCULATE(SUM(Aging_table[TotalAmount]),Aging_table[ClassLegal]="L",Aging_table[TotalAmount]>0, ,filter(DimDate, DimDate[Date]=DimDate[FirstDay EndOfQtr])),

"Year", CALCULATE(SUM(Aging_table[TotalAmount]),Aging_table[ClassLegal]="L",Aging_table[TotalAmount]>0, filter(DimDate, DimDate[Date]=DimDate[FirstDay EndOfYear])),

CALCULATE(SUM(Aging_table[TotalAmount]),Aging_table[ClassLegal]="L", Aging_table[TotalAmount]>0,))

Based on above query Output is 

 

FirstDay EndOfQtr = STARTOFMONTH(ENDOFQUARTER(DimDate[Date])) used in above query

FirstDay EndOfYear = STARTOFMONTH(ENDOFYEAR(DimDate[Date])) used in above query

Date Table

I want to calculate end of last year bal and end of previous quarter balances as below

When Year

when Qtr

Please help me to write measure.

Regards

Samar

[amitchandak · Accepted solution]

@Anonymous , You have a few functions to give end of qtr/year balance - closingbalancemonth, closingbalancequarter, closingbalanceyear

Last period closing balance - openingbalancemonth,openingbalancequarter, openingbalanceyear

refer my video for that -

https://www.youtube.com/watch?v=yPQ9UV37LOU

https://www.youtube.com/watch?v=6lzYOXI5wfo

Or use time intelligence for complete data

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))