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.
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.
the database Id
The Business ID
The date of the transaction
The amount of the transaction
The sum of the settlement, from the selected point in time but linked back to the fct on the table id