Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

last year december balance and end of previous quarter balance

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 
 SamarK_1-1617172062534.png
FirstDay EndOfQtr = STARTOFMONTH(ENDOFQUARTER(DimDate[Date])) used in above query
FirstDay EndOfYear = STARTOFMONTH(ENDOFYEAR(DimDate[Date])) used in above query
 

Date Table

SamarK_0-1617170923351.png

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

When Year

SamarK_2-1617172112938.png

when Qtr

SamarK_3-1617172401428.png

 

Please help me to write measure.

 

Regards

Samar

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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"))

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@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"))

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.