Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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.
Regards,
Daniel
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.
Regards,
Daniel
Thank you!
it worked 🙂
Hi @iti_il,
It's pleasant that your problem has been solved, could you please mark my reply as Answered?
Regards,
Daniel He
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
87 | |
85 | |
74 | |
69 | |
65 |
User | Count |
---|---|
214 | |
124 | |
117 | |
82 | |
76 |