Beginning balance

[Anonymous]

This has to be a classic problem, but I have been completely stopped by this calculation.  Below is some sample data.  The dim.Date table primary relation is to the Wdate.  There is also a secondary relationship to the WIPdate (same dim.Date table).  I wish to calculate both the beginning and ending balances of the values.  In the case below, when the user picks a filter of January 2019, that, of course, limits the records to only those that have a Wdate in January.  

To explain further, this is a table of time entries where Wdate is the date worked and WIPdate is the date billed.  I wish to report the unbilled time entries at a point in time - the day before the user selected date range.  So, in words, the definition of unbilled time is time worked before the first date selected by the user, but not billed by that first date.

Beginning Balance:=CALCULATE(SUM(Value),Wdate<FIRSTDATE(dim.Date[date]),WIPdate>=FIRSTDATE(dim.Date[date]))

The formula seems sound and when I hard code it with dates it produces the number I am seeking viewed within the model.  Where I am stopped, I believe, is that the date filter the user selects excludes all the dates in my formula because they are not within January (in my example).  So Power BI shows blanks.  I have been beating up SUMX, ALL, FILTER, DATESBETWEEN, etc. with no success.  I added columns to my dim.Date table of "the day before" (dim.date[Date]-1) and "beginning of time" (DATE(1900,1,1)).  Those didn't help.  You can see I am grasping here.  Maybe gasping is a better word.  Thanks for your help. 

Wdate	WIPdate	Value
11/15/2018	12/5/2018	10
11/17/2018	12/5/2018	15
12/2/2018	12/28/2018	25
12/12/2018	12/28/2018	20
12/18/2018	1/12/2019	15
12/31/2018	1/12/2019	10
1/5/2019	1/31/2019	5
1/8/2019	1/31/2019	15
1/9/2019	1/31/2019	30
1/15/2019	2/5/2019	25
1/17/2019	2/5/2019	15

BTW, this example should return 25 as the beginning balance.  The only two entries that should add to the sum are these two because they were both worked before 1/1, but not billed until after.  However, they won't be shown because the Wdate is not in Janaury.  How do I report this result of 25?

Wdate	WIPdate	Value
12/18/2018	1/12/2019	15
12/31/2018	1/12/2019	10

[Anonymous]

how to you calculate dim.date for below mentioned formula, Please help

Beginning Balance:=CALCULATE(SUM(Value),Wdate<FIRSTDATE(dim.Date[date]),WIPdate>=FIRSTDATE(dim.Date[date]))

[v-yulgu-msft]

Hi @Anonymous ,

Please remove all relationships between data table and dim date table. Above measure would work.

Regards,

Yuliana Gu

[Anonymous]

@v-yulgu-msftI do not follow your suggestion.  It sounds simple enough, but how does one "remove the relationship" for a single calculation?  Maybe I need to do more research?

[Anonymous]

@v-yulgu-msftHow does one remove all relationships between the data and date tables without rending user date filters useless?  Clearly I am not understanding your recommendation.

[danno · Accepted solution]

I've taken a look and created a power bi file with your model and relationships.  I've used variables in the measures to identify which amounts have been worked in a selected period and which have been paid based on the WDate and the WIPDate.  The Unpaid Amount decreases to zero once you reach the end of the date range as one the final WIP Date, all amounts worked are paid.  

Here are the formulas: 

Amount Worked at start of Period =

VAR MinSelectedWorkingDate = MIN('Calendar'[Date])

RETURN (CALCULATE(SUM(Data[Value]), FILTER(ALL(Data), Data[WDate] < MinSelectedWorkingDate)))

Amount Worked in Period = CALCULATE(SUM(Data[Value])) // This uses the default relationship from WDate to Calendar to ensure amount is in the selected period

Amount Worked at end of period = [Amount Worked at start of Period] + [Amount Worked in Period]

Here are the Billed amounts:

Amount Billed at start of Period =

VAR MinSelectedWorkingDate = MIN('Calendar'[Date])

RETURN (CALCULATE(SUM(Data[Value]), FILTER(ALL(Data), Data[WIPDate] < MinSelectedWorkingDate)))

Amount Billed in Period = CALCULATE(SUM(Data[Value]), USERELATIONSHIP('Calendar'[Date], Data[WIPDate]))

//This uses the USERELATIONSHIP function to switch to the inactive relationship from WIPDate to Calendar Date

Amount Billed at end of Period = [Amount Billed at start of Period] + [Amount Billed in Period]

Here are the amounts outstanding:

Amount Owed at Start of period = [Amount Billed at start of Period] - [Amount Worked at start of Period]

Amount Owed at End of Period = [Amount Billed at end of Period] - [Amount Worked at end of period]

I hope this solves your problem. - please mark as Solved 🙂

[Anonymous]

@dannoI inserted an ALLEXCEPT into the formula...

Amount worked at start of period:=

VAR MinSelectedWorkingDate = MIN('Date'[Date])

RETURN (CALCULATE([Sum of Wfee]+[Sum of Wexp],

FILTER(ALLEXCEPT(WIP,WIP[ClientKey],WIP[ClientEngKey]),WIP[Wdate]<MinSelectedWorkingDate)))

...and the numbers react now to dropping ClientEngKey onto the pivot rows.  However, the user will pick the "name" from the dim.Client table and the ALLEXCEPT only allows for filtering directly in the fact table (WIP).  Progress though.

One more question - what's the advantage of using the variable rather than just typing MIN('Date'[Date]) after the < sign at the end of the formula?  Best practice?  Performance?  

[Anonymous]

@dannoThis may help explain my further dilemma.  Here is a screen shot of my test pivot table against my SSAS tabular model using your formulas.  

[Anonymous]

@dannothanks!  Sublimely clever.  I understand each, but I am facing a new problem that I think is created by ALL.  To be fair, I did not include a dimension that is important.  Each of those time entry lines is assigned to a client.  I need to come up with the beginning and ending balances by client.  However, if I am thinking correctly, the ALL command removes the relationship between the client dimension and the filter.  Therefore I get a pivot table with the same value for every client ID.  Except in the two cases of "In Period".  Can you see a way around this new issue?