topic Re: Calculate (partial) amount between two dates in DAX Commands and Tips

Calculate (partial) amount between two dates

Päät — Mon, 04 Mar 2024 10:08:30 GMT

Hello,
I would like to create a measure to sum up the amount of projects between a specified date range by using a slicer. See an example table below. If the chosen date range is not covered by the project duration, it shall not be counted respectively if it covers only partially, then the corresponding fraction of the project amount shall be used.
E.g. slicer date is set from 01.11.2023 to 31.03.2024, then I would expect a calculation like this:

3/6 * 10.000 + 5/6*15.000 + 3000 + 1.5/6.5 * 9.000 = 22.576,92

Projectname	Start	End	Amount
A	01.01.2024	31.05.2024	10.000
B	01.10.2013	31.03.2024	15.000
C	15.01.2024	15.02.2024	3.000
C	16.02.2024	31.08.2024	9.000

For the dashboard, I would like to use the table visual to see the aggregated amount of each project as well as the aggregated amount of all project. There are other attributes as well e.g department etc.. The measure shall also work when I want to use those attributes for filtering the table visual.

I hope there is someone who can help me as this issue drives me crazy 😄

Greetings,
Patrick

Re: Calculate (partial) amount between two dates

olgad — Mon, 04 Mar 2024 10:56:35 GMT

Hi, check this out,

first you wanna calculate the difference in days you have between start and end date of the project.

then you want to see the difference in days between the start date and the min date of the slicer as well as end date of the project and max date of the slicer. In the end, if any of those date differences are negative, meaning the slicer dates are before or after the project, you want to subtract those days.

I used 30 as a monthly average of days to calculate the proportions.

Datediff = DATEDIFF('Table'[Start],'Table'[End], DAY)

Partial Amount =

var DateDiffStart=Calculate(DATEDIFF(min('Date'[Date]),min('Table'[Start]),DAY))

var DateDiffEnd=Calculate(DATEDIFF(min('Table'[End]),max('Date'[Date]),DAY))

var StartDate=if(DateDiffStart<0, DateDiffStart, 0)

var EndDate=if(DateDiffEnd<0, DateDiffEnd, 0)

var ProjectDaysInSlicerPeriod=DIVIDE(Sum('Table'[Datediff])+StartDate+EndDate, 30)

var ProjectMonth=Divide(Sum('Table'[Datediff]), 30)

RETURN

DIVIDE(ProjectDaysInSlicerPeriod, ProjectMonth)*Sum('Table'[Amount])

Remark: for Project A, it is not 3/6, but 3/5

Re: Calculate (partial) amount between two dates

Päät — Tue, 05 Mar 2024 14:05:51 GMT

Hello Olgad,
Thank you very much for your very fast reply, it gave me some good hints to solve my issue.
In your implementation of the ProjectDaysInSlicerPeriod, I had the issue that I got negative values if e.g. the start of the slicer was bigger then the end date of the project. Thus I created some correction values in a similiar manner using the DATEDIFF function:

VAR DateDiffCheckEnde = CALCULATE(DATEDIFF(min('Table'[End]),min('Date'[Date]), DAY))

VAR DateDiffCheckStart = CALCULATE(DATEDIFF(max('Date'[Date]),min('Table'[Start]),DAY))

Var Corr1 = if(DateDiffCheckEnde >0 , DateDiffCheckEnde, 0)

Var Corr2 = if(DateDiffCheckStart > 0, DateDiffCheckStart, 0)

var ProjectDaysInSlicerPeriod=DIVIDE(Sum('Table'[Datediff])+StartDate+EndDate+Corr1+Corr2, 30)

Re: Calculate (partial) amount between two dates

Anonymous — Thu, 07 Mar 2024 06:15:35 GMT

Hi Päät ,

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Yilong Zhou

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