Need urgent help - DAX calculation

[Kolumam]

I have the below table.

Start Date of Contract	End Date of Contract	Operation Name	Comprehensive O&M Price
1/7/2019	31/8/2019	X	375
1/9/2019	31/3/2020	X	405
12/5/2020	31/12/2021	X	385
1/4/2017	31/3/2018	Y	380
1/4/2018	31/3/2020	Y	410
11/5/2020	31/12/2021	Y	303

Based on the start date of contract and end date of contract for each Operation Name, I need to calculate the annual contract for each operation name.

Expected Output with Calculation: 

Operation Name	Year	Annual Contract	Calculation
X	2019	395	(375*60/180)+(405*120/180)
X	2020	353.01	(405*90/365)+(385*240/365)
X	2021	385	(385*365/365)
Y	2017	380	(380*365/365)
Y	2018	396.97	(380*90/365)+(410*270/365)
Y	2019	410	(410*365/365)
Y	2020	300.23	(410*90/365)*(303*240/365)
Y	2021	303	303*365/365

Thanks in advance for the help.

@amitchandak @parry2k @mahoney19 @Amit @amitchandak @parry2k @az38 @jdbuchanan71 @mahoneypat @edhans @harshnathani @v-kellya-msft @MFelix @Ashish_Mathur @BA_Pete @ryan_mayu @kbuckvol @Alexander76877 @Petazo @Mariusz @TomMartens @Greg_Deckler @tjd @Sean @mikstra @AllisonKennedy @EricHulshof @briandpeterson @USG_Phil @vpatel55 @mwegener @v-piga-msft @tex628 @sturlaws @Vvelarde @CheenuSing @MarcelBeug @Zubair_Muhammad @v-piga-msft @danextian @MattAL @MattAllington @roalexan @Alexander76877 @kgc 

[Greg_Deckler]

@Kolumam - Can you explain the logic behind the calculations that you have presented?

So, for example, 

(375*60/180)+(405*120/180)

So, I get where the 375 and 405 come from. Is the 60 and 120 the number of months within 2019 for which the contract is valid and you are assuming 30 day months? Where does the 180 come from? I would think 60 + 120 but then the next line is:

(405*90/365)+(385*240/365)

And 90 and 240 do not add up to 365. 

Then you have the 2021 stuff where suddenly you switch to 365/365 (which is 1) soooooo.... Puzzled.

All of that said, you are going to probably end up needing to use something like GENERATE since if you solve this in DAX because you need to essentially "invent" rows in a table and there are limited options for doing things like that.

[Kolumam]

Hi @Greg_Deckler 

Please find my explanation for the calculation.

(375*60/180)+(405*120/180)

Here 60 is the number of days for which the contract is valid (end date - start date) and 180 is the total number of days between 1 July 2019 and 31st December 2019. I am using the days approximately but ideally it should be the exact number of days.

For this one: (405*90/365)+(385*240/365)

90 because the contract is from 1st Jan 2020 to 31 March 2020 and 240 is because the the start date is 12/5/2020 and ends at 31st Dec 2020. I am taking rough numbers. Not the exact difference in days.

For the last contract, the contract applies for whole year. Hence 385*365/365

[Mariusz]

Hi @Kolumam 

if you are looking to split the rate proportionally by days then consider the attached solutions using Power Query and DAX table

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Kolumam]

Hi @Mariusz 

Thanks for trying but your solution is incorrect. See below.

For 2019, you are taking the whole year, rather you should only take the number of days between 1st July 2019 and 31st Dec 2019. So it will be (375*60/180)+(405*120/180) which is 395. As you can see for contract X, we have two contracts on the same year. If that happens, then you need to divide by the number of days from the start date of contract to end of the year of start date of contract. If there are no two contracts in a year, say for example, the start date is 1st April 2017 for contract Y, in that case for 2017, it should take the entire year. So it will be (380*365*365) which is 380 instead of 286.83. From 2018, the value is prorated accordingly. 

Do you get it?

[Kolumam]

Hi people, 

My question is still not solved yet. Can anyone please help?

@parry2k @mahoney19 @Amit @parry2k @az38 @jdbuchanan71 @mahoneypat @edhans @harshnathani @v-kellya-msft @MFelix @Ashish_Mathur @BA_Pete @ryan_mayu @kbuckvol @Alexander76877 @Petazo @Mariusz @TomMartens @Greg_Deckler @tjd @Sean @mikstra @AllisonKennedy @EricHulshof @briandpeterson @USG_Phil @vpatel55 @mwegener @v-piga-msft @tex628 @sturlaws @Vvelarde @CheenuSing @MarcelBeug @Zubair_Muhammad @v-piga-msft @danextian @MattAL @MattAllington @roalexan @Alexander76877 @kgc 

[ImkeF]

Hi @Kolumam ,

as the others have mentioned already, the problem with the sample you've provided is that there are inconsistencies/errors in it that make determine the desired logic hard.

The following code calculates proportional values according to the actual number of days in the year: 

let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "dc1LCsAwCATQu7gOqPkQc5OW4P2vUQ2RhkJ3M/IY5wTGjpl4QILCKJEv772BJifjIMVypk0qbZKxxd2MVSscOxI71Xf6u8Ni+V6ETiKfV04qb8I/r9YOFVB9AA==", 
                    BinaryEncoding.Base64
                ), 
                Compression.Deflate
            )
        ), 
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table[
                #"Start Date of Contract" = _t, 
                #"End Date of Contract" = _t, 
                #"Operation Name" = _t, 
                #"Comprehensive O&M Price" = _t
            ]
    ),
    #"Changed Type" = Table.TransformColumnTypes(
        Source, 
        {
            {"Start Date of Contract", type date}, 
            {"End Date of Contract", type date}, 
            {"Operation Name", type text}, 
            {"Comprehensive O&M Price", Int64.Type}
        }
    ),
    AddListOfYears = Table.AddColumn(
        #"Changed Type", 
        "Year", 
        each {Date.Year([Start Date of Contract])..Date.Year([End Date of Contract])}
    ),
    #"Expanded ListOfYears" = Table.ExpandListColumn(AddListOfYears, "Year"),
    AddStart = Table.AddColumn(
        #"Expanded ListOfYears", 
        "Start", 
        each List.Max({[Start Date of Contract], #date([Year], 1, 1)})
    ),
    AddEnd = Table.AddColumn(
        AddStart, 
        "End", 
        each List.Min({[End Date of Contract], #date([Year], 12, 31)})
    ),
    AddDuration = Table.AddColumn(
        AddEnd, 
        "DurationInDays", 
        each Duration.Days([End] - [Start]) + 1, 
        Int64.Type
    ),
    AddDaysInYear = Table.AddColumn(
        AddDuration, 
        "DaysInYear", 
        each if Date.IsLeapYear(#date([Year], 1, 1)) then 366 else 365
    ),
    AddAnnualShare = Table.AddColumn(
        AddDaysInYear, 
        "AnnualShare", 
        each [DurationInDays] / [DaysInYear]
    ),
    WeightPriceByShare = Table.AddColumn(
        AddAnnualShare, 
        "AnnualContractValue", 
        each [#"Comprehensive O&M Price"] * [AnnualShare], 
        type number
    )
in
    WeightPriceByShare

It creates a table in the query editor that will hopefully allow you to follow the logic and see where your numbers are wrong:

In general your numbers are too low because your using 365 days a year and weight your months only with 30 days (adding up to 360).

A specific inconsistency in your calculation is that you allocate partial end years pro rata (i.e. Y 410 for 2020: 90 days (Jan-Mar)) but all partial start years with full year (Y 380 with 365 days, although the contract starts in April and X 395 divided by only 180 (instead of 360)). 
So my calculation assumes that the "Comprehensive O&M Price" is an annual fee that has to be allocated to partial years consistently.