Invoiced and paid amounts from same table with different dates

[ITManuel]

Hi all,

I've got a fact table in which I have Invoiced amounts with dates and paid amounts with dates. 

Project

Invoiced

Invoice date

Paid

Payment date

I would like to visualize in a column chart the invoiced amounts and paid amounts per months or dates. I've got a common calendar table connected to either invoice date or payment date from the fact table. 

Visualizing Invoiced and Paid in the same chart with either Invoice date or Payment date connected to the common date table puts Invoiced and Paid to the same months/date as there is only a connection to either of the dates. (Invoiced and paid dates are significantly different)

I worked around this issue by duplicating the fact table connecting the common calendar to Invoiced date in the original table and to Payment date in the duplicated table. Now it works, but I'm asking myself if there isn't a more elegant ways around this or if there is something which I'm doing fundamentaly wrong?

Thanks in advance 

Best regards

[v-yingjl · Accepted solution]

Hi @ITManuel ,

Suppose you are using the relationship between the calendar[date] and Table[Paid] column, you can keep the inactive relationship between calendar[date] and Table[Invoiced], just like this:

Then you can create use Userelationship() to create a measure like this instead of duplicating the fact table, put it with [Paid] column in the column chart, you should get the expected result:

_Invoiced = 
CALCULATE(
    SUM('Table'[Invoiced]),
    USERELATIONSHIP('Table'[Invoice date],'Calendar'[Date])
)

Attached a sample file in the below, hopes to help you.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[ITManuel]

Hi v-yingjl,

great help.

Thank you & Best regards