Determine if start date and end date are in the same period - DAX

[iti_il]

Hey,
I have a table with an END DATE and a START DATE (Table 1) and an additional table with a RATE per period (Table 2).
I have to check whether the START DATE and the END DATE in Table 1 are both in the same period, as indicated in Table 2. If the answer is TRUE;

Multiply the relevant RATE with the column QUANTITY in Table 1.
If not;

calculate the number of days in each period relative to the total number of days,

 And then multiply each result at the relevant rate, sum the results (weighted average) and multiply the answer in the QUANTITY column in Table 1.

For example:

TABLE 1
#	START DATE	END DATE	QUANTITY
1	12/20/2017	02/20/2018	200
2	10/01/2017	12/30/2017	150

TABLE 2
START DATE	END DATE	RATE
06/01/2017	12/31/2017	7.5$
01/01/2018	05/31/2018	8.5$

If true (#2): 150*7.5=1,125
If false (#1): (10/60*7.5)+(50/60*8.5)=8.33$
8.33*200=1,666.67

Thanks.

[v-danhe-msft · Accepted solution]

Hi @iti_il,

Based on my test, you can refer to below steps:

1.Create an Index column in your ‘Period’ table.

2.

2.Create two measures to calculate different situations.

True = (CALCULATE(SUM(Table1[QUANTITY]),FILTER('Table1','Table1'[START DATE]>=MIN('Table2'[START DATE])&&'Table1'[END DATE]<=MAX('Table2'[END DATE])&&'Table1'[#]=MAX('Table2'[Index]))))*MIN('Table2'[RATE])

Fales = var  c=(CALCULATE(SUM(Table1[QUANTITY]),FILTER('Table1','Table1'[START DATE]>=MIN('Table2'[START DATE])&&'Table1'[END DATE]<=MAX('Table2'[END DATE])&&'Table1'[#]<>MAX('Table2'[Index]))))

            Var a=DATEDIFF(CALCULATE(MIN('Table1'[START DATE]),FILTER('Table1','Table1'[QUANTITY]=c)),CALCULATE(MAX('Table2'[END DATE]),FILTER('Table2','Table2'[Index]=MIN('Table1'[#]))),DAY)-1

            var b=-DATEDIFF(CALCULATE(MAX('Table1'[END DATE]),FILTER('Table1','Table1'[QUANTITY]=c)),CALCULATE(MIN('Table2'[START DATE]),FILTER('Table2','Table2'[Index]<>MIN('Table1'[#]))),DAY)

            return ((a/(a+b)*MIN(('Table2'[RATE])))+(b/(a+b)*MAX('Table2'[RATE])))*c

3.Create two Card visual to show the data, now you can see the correct result.

You can also download the PBIX file to have a view.

Hehttps://www.dropbox.com/s/q0v9ooiivzgynfg/Determine%20if%20start%20date%20and%20end%20date%20are%20i...

Regards,

Daniel 

[iti_il]

Thank you!
it worked 🙂

[v-danhe-msft]

Hi @iti_il,

It's pleasant that your problem has been solved, could you please mark my reply as Answered?

Regards,

Daniel He