cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nikilouw
Frequent Visitor

Opening Balance where Month periods are not calendar date

-I am trying to create a measure that calculates an opening balance for each month.
-The month periods differ i.e. not all months are 1st to 30/31st
-I have a date table where the month periods are defined eg January is 25 Dec to 23 Jan, Feb is 24 Jan to 22 Feb

nikilouw_0-1595576419434.png

- The fact table is joined via the "PeriodNoSK" dimension:

nikilouw_1-1595576569565.png

- the opening balance would thus be all rows with PeriodNoSK up till the PeriodNoSK -1 per the DateTable


Can anybody help met please?

5 REPLIES 5
amitchandak
Super User IV
Super User IV

@nikilouw , Can you share sample data and sample output in table format?

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi @amitchandak 
Thank you for the prompt response.
I hope this helps.

nikilouw_0-1595579871820.png

 

Hi @amitchandak 

Could you have a look at my response. please?

 

Thank you in advance

@nikilouw , Can you share sample data and sample output in table format? Seem like need write some code 😀



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandak 

definetely need code 🙂
I cant share my data, but I have given an example I did in Excel in the thread above - can you work from this?

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

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.