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

CLOSINGBALANCEYEAR

Hi all,

 

I'm trying to create a modification to something like the CLOSINGBALANCEYEAR function.

 

I need to see the total value of a budget between 2 periods. Seems fairly easy but the issue i'm facing is i need to show the grandtotal between the periods at all times.

 

the point is to have a grand total modified by user context for the selected months without having to create a MTD, QTD and YTD value.

 

This example shows between months 1 to 3; but i'd need the solution to show me the grand total between dates 2 to 7 (example only) if the user selected it.

 

The schema is a fairly straight forward fact table showing fact.[DATE] and fact.[product], with both a dim.[date] and dim.[product] tables separate.

 

Any help is really appreciated, i'm having a lot of troubles getting the needed visual!

 

 

 

1.png

 

2.png

 

2 REPLIES 2
Community Support Team
Community Support Team

Re: CLOSINGBALANCEYEAR

Hi @NathanBooth 

In the two screenshot, "Budget for selected period" is the grand total between dates 2 to 7 (example only) if the user selected it, It is your expected result, right?

In the "Budget" column, values only exsits in the first day of each month, it means for each whole month instead of one day, right?

For example, for month1, it has a budget 100, for month2, it has a budget 150.

 

Best Regards

Maggie

NathanBooth Frequent Visitor
Frequent Visitor

Re: CLOSINGBALANCEYEAR

It’s right it is a budget by period and each budget is by a single day only to represent the entire month.

The issue is I need to show the full budget for all selected period at each month, so the full budget as at Jan is equal to the total budget across months -, 2 & 3