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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
heman_powerbi
Regular Visitor

Time Intelligence : YoY Closing Balance Look with drilldown on date hierarchy

Have a simple table like a date and amount. The amount is NOT purchase activity but a mortgage ENDING BALANCE. hence if I sum it across time periods we will get values that are not true ending balance.

 

My objective 

- show ending balance and its sameperiodlastyear() balance

- along with drill down behaviour by year and month

 

THe problem is 

1. how do you calculate the ending balance where my context could be month or year in teh same report.

2. if im in monthly timescale, the ending balance should be based on monthly value

3. if im in yearly timescale, they should be ending balance based on yearly value

 

 

sample.png 

3 REPLIES 3
v-yuezhe-msft
Employee
Employee

@heman_powerbi,

Firstly, in your original table, create the following measures.

ClosingBalanceYear = CLOSINGBALANCEYEAR( SUM( Table[amount]), Table[date])
ClosingBalanceMonth = CLOSINGBALANCEMONTH( SUM( Table[amount]), Table[date])

Then create the following columns.
Year = YEAR(Table[date])
Month = MONTH(Table[date])

Secondly, create a new table using DAX below.

BalanceForYear = SUMMARIZE(Table,Table[Year],"YearBalance",[ClosingBalanceYear])

Then create the following columns in the new table.

Index = CALCULATE(COUNT('BalanceForYear'[Year]),ALL('BalanceForYear'),FILTER('BalanceForYear','BalanceForYear'[Year]<=EARLIER('BalanceForYear'[Year])))

Previous year = IF('BalanceForYear'[Index]=1,'BalanceForYear'[YearBalance],LOOKUPVALUE('BalanceForYear'[YearBalance],'BalanceForYear'[Index],'BalanceForYear'[Index]-1))

YOY = 'BalanceForYear'[YearBalance]-IF('BalanceForYear'[Index]=1,'BalanceForYear'[YearBalance],LOOKUPVALUE('BalanceForYear'[YearBalance],'BalanceForYear'[Index],'BalanceForYear'[Index]-1))

2.PNG


Thirdly, create another new table using  the following DAX.
Balance for month = SUMMARIZE(Table,Table[Year],Table[Month],"MonthBalance",[ClosingBalanceMonth])
1.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Appreciate your response. Will try to understand the logic for each of your steps. Looks like you are an expert with DAX. I want to ask a follow up question. I can use CALCULATE function well but I am sure there is grey area in my understandng. I am confused on the arguments and return types for Filters in CALCULATE function. I understand the way the contexts are changed. But I want to understand the arguments and return types procesing.

 

Can you give me a good reference which explains CALCULATE function in a simplistic manner? Or your explanation if you have time?

@heman_powerbi,

You can refer to the following blogs to learn more about Calculate function.

http://sqlblog.com/blogs/marco_russo/archive/2010/01/03/how-calculate-works-in-dax.aspx
https://www.sqlbi.com/articles/filter-arguments-in-calculate/

Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors