Forum Discussion
Beginning balance
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 periodAmount 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 DateAmount 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 :)
Hi Anonymous ,
Please remove all relationships between data table and dim date table. Above measure would work.
Regards,
Yuliana Gu
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.
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?