Calculating number of days between two dates

[Anonymous]

Hi.

I have an Invoice table with Invoice_Startdt, Invoice_Enddt and Amount.
The period of this amounts can be from days to thre or four months.

What I want to do in a measure is:
Find how many days there is between the dates (Startdt - Enddt)

Divide the Amount from Amount on the number of days betweeen the dates.

Ex:
Start_dt: 01.01.2017

End_dt: 30.01.2017

Amount: 499

Days between dates: 30

499 / 30 = 16,63

So I want a measure who find 16,63

Best regards

Marius

[Anonymous]

Hi @Anonymous

Do you need an extra column in your table per row or a measure?

[Anonymous]

Hi @Anonymous

Thanks for your reply.

I think I only need a measure, but would it be to much if you comes with a suggestion on both?

That would be great.

Best regards
Marius

[Anonymous · Accepted solution]

Here is a Solution in Power Query, which adds a new column in the table:

    #"Inserted Age" = Table.AddColumn(#"Changed Type", "Duration", each 1 + Number.From([End_dt] - [Start_dt]), type number),
    #"Added Custom" = Table.AddColumn(#"Inserted Age", "Custom", each [Amount]/[Duration])

The first line calculates the difference in days. The second divides the amount per line by the number of days.

DAX Solution (Measure):

DAX_Measure = 
DIVIDE(sum(Tabelle1[Amount]);
(DATEDIFF(min(Tabelle1[Start_dt]);MAX(Tabelle1[End_dt]);DAY)+1))

But I don't think, that the DAX measure is the way to go, as your calculation is just semi-additive. Up to you which one works for your case.

Hope this helps.

[Anonymous]

@Anonymous Thank you so much for your time!
The new columns workd fine, just what I wanted!

Have a nice day 🙂

Best regards
Marius