Forum Discussion

Accelins's avatar
Accelins
Frequent Visitor
3 years ago

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 i...
Need Help
amitchandak's avatar
amitchandak
Super User
3 years ago

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-trend/ba-p/882970

 

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])))

 

 

  • Accelins's avatar
    Accelins
    Frequent Visitor
    3 years ago

    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