Distributing sum of multiple values depending of start and end date over selected date/month/year

[Anonymous]

Hello together,

currently I'm trying to calculate the part of invoices over a selected date.

All invoices have a sepcific start and end date.

How can I "distribute" my value over the selected date/month/year ?

E.g:

INVOICETABLE:

Invoice-Nr       Start                End                   Value

    I-1           01.01.2019     21.03.2019           2000,00€ 

    I-2           03.04.2019     02.05.2019           3000,00€

    I-3           05.05.2019     08.07.2019           5000,00€

    ....

DATETABLE:

     Date             Year      Month

01.01.2019        2019         1

02.01.2019        2019         1

.

.

.

31.12.2020        2020        12

I have tried it with:

CALCULATE(

                    SUM(Invoicetable[Value]);

                    DATESBETWEEN(

                                               Datetable[Date];

                                               Invoicetable[Start];

                                               Invoicetable[End])

                    )

But of course this is just working if there would be only one invoice and there wouldn't be a distribution.

Can me help me somenone with this problem? Is there a way to solve it easy and dynamic with a measure?

Thank you very much in advance!

BR,

Sven

[v-xicai]

Hi @Anonymous ,

You can try to create measure like DAX below.

measure1 = CALCULATE(SUM(Invoicetable[Value]),FILTER(ALLSELECTED(Invoicetable),Invoicetable[Invoice-Nr]=MAX(Invoicetable[Invoice-Nr])&&Invoicetable[Start]<=MAX(Datetable[Date])&&Invoicetable[End]>=MIN(Datetable[Date])))

You can choose Clustered column chart visual to display the result, drag the Datetable[Date] on Axis and drag the measure1 on Value .

If I misunderstood it, could you please share your sample data or desired output screenshots for further analysis, you can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Amy

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

[Anonymous]

Hi @v-xicai ,

thank you very much for your answer! But unfortunately it hasn't the result I need.

With my OneDrive Business it isn't allowed to share files public. But I've uploaded my InvoiceTest PBI File to our Company FileShare Cloud. You can download it here:

https://p3filecloud.p3-group.com/Download/054ea088602daf3ea88e802c61e27e

I've created this invoicetable together with the calculated column "value per day":

Datetable is calculated with "Datetable = CALENDERAUTO()":

With your measure I've got the following result. 

But I would like to distribute my values over the different months.

For example for my I-1 I've a value of 1000 for 01.01.2019 till 15.02.2019. 

So it would be 688,82 in January 2019 and 333,3 in February. If there are other Invoice with days for January or February it should sum it.

Hope you know what I mean?

Thank you in Advance!

BR,

Sven

[Anonymous · Accepted solution]

Hello @v-xicai ,

I did it now with a calculated column and the value per day:

SUMME = CALCULATE(SUM(InvoiceTable[Value per Day]);FILTER(InvoiceTable;Datetable[Date]>=InvoiceTable[Start] && Datetable[Date]<=InvoiceTable[End]))

So I get my result like this. If you know a better solution with a measure, just tell me 🙂 Otherwise, anyway thank you!