Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Beginning balance

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

AnonymousI 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?