cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ajayemmadi1
Helper I
Helper I

DAX expression for Opening Balances, addition, deletion and closing balance for each month

Hi Guy's,

I am working on School Dashboards. I want to know how many members joined in a month? How many left in the same month and what is the total for the same month? I am looking for DAX expression for opening, addition, deletion and closing balance of no. of students. I have done for 1 month using hardcode in DAX expression but I want the same for all months. Can anyone guide me to create a variable for my issue. 

Suppose Total no. of Students tillJuly are 220, then in August month the following should be shown. 
Opening = 220 (closing of June)
Addition = 34
Deletion = 10
Closing = 244
Now, the closing Balance 244 should be opening for September month and vice versa..

 

 

7 REPLIES 7
mehmet
Frequent Visitor
ajayemmadi1
Helper I
Helper I

Can anyone please help me out of this?

 

Reagrds,

Ajay

Anonymous
Not applicable

If you have a dedicated date table you can leverage the built-in time intelligence functions of CLOSINGBALANCEMONTH and OPENINGBALANCEMONTH

 

Hi Nick,

I have tried but didn't get expected results. Can you tell me any other way to resolve it.

Anonymous
Not applicable

can you upload some sample data and what you would like the expected output to be?

In the below images I have month wise trend of employess count. 

 

Employee MIS.png

When I drill through the chart, it should take me to another page and show the breakup of Aug month like opening count of employees, how many employees are added, deleted and closing for the same month. 

Emp MIS.png

I have done using hardcode formula for Aug month. Similary I need for all months which can give automatic results when we do drill through month wise. Now when I drill through for Sep month, the result page should show the breakup of only Sep month and vice versa..

Hi Guy's,

 

Can anyone look into the above issue and please provide resolution or any idea on the same.

 

Regards,

Ajay Emmadi

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors