Forum Discussion

Päät's avatar
Päät
Frequent Visitor
2 years ago
Solved

Calculate (partial) amount between two dates

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 ...
  • olgad's avatar
    olgad
    2 years ago

    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

Päät's avatar
Päät
Frequent Visitor
2 years ago

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)
 
 
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.