Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Accelins
Frequent Visitor

Point in Time balance

Hello, 

I have a star schema set up with a transaction fct table, a transactional settlement table, and a few shared dimensions. The fact table is essentially an invoice and the settlement table is payments against the invoice. 

I would like to figure out a point in time balance by a dimension (dim_premium_transactions) but haven't been able to get it into a table as yet. the measure in a card filtered to one ID works but when I add the measure into a table with the userelationship function. it doesn't filter the tables by the point in time. 

If I add in the other 2 relationships as separate UserRelationships I get the ambiguity error

 

The perfect outcome would be, the PIT table filters both fct and settlement first, then the DAX calculates the SUM of the fct minus the Sum of the settlement.

 

Thanks for any help! 

PIT Model.jpg

PIT.jpg

PIT UR.jpg

2 REPLIES 2
amitchandak
Super User
Super User

@Accelins , if you have start data and end date, you need to follow HR approach. else inventory approch

 

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

Inventory

Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw

 

Inventory / OnHand
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

 

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Hi @amitchandak I reviewed both videos and read the Blog but non of these are suitable for the issue we have. 

I need to be able to display the balance in a table with the fct information. Therefore I need to include the inactive relationship between the fct and the settlement, the inventory returns the same result as I already have. 

Table IdBusiness IdFct DateAmountBalance
the database IdThe Business IDThe date of the transactionThe amount of the transactionThe sum of the settlement, from the selected point in time but linked back to the fct on the table id

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.