Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, How can we calculate the total days in below table where we want to add up the days from each week, but only until we find the first week where the number of days is less than 7. Once we find that week, we'll stop adding up the days. So, we're summing up the days, but we'll only include weeks where the days are less than 7 until we hit the first week where the days are less than 7, and then we'll stop.
like in this case result should be 33.6 days because we are adding days till week 4 and week 4 is first week where days are less than 7 days.
Week | Days |
- | 7.0 |
1 | 7.0 |
2 | 7.0 |
3 | 7.0 |
4 | 5.6 |
5 | 7.0 |
6 | 7.0 |
7 | 7.0 |
8 | 7.0 |
9 | 1.0 |
10 | 7.0 |
Solved! Go to Solution.
Assumption : Need unique key for this data. I am assumin that combinatio of code and Week is unique.
Using the data which you shared, please create this measure.
Hi @talespin i am having an below error may be because my week[days] is measure instead of column, is there any way to resolve this error.
Hi @talespin below is the formula i have been using for days calculation and i want stop this calculation at week 4 as per the above condition we have discussed.
Days formula-
Data for your Ref
Code | Inventory | Week | Revenue | Cumulative Revenue | Remaining Inventory |
55 | 7,31,71,800 | 0 | 58,17,471 | 58,17,471 | 6,73,54,309 |
55 | 7,31,71,800 | 1 | 73,29,755 | 1,31,47,226 | 6,00,24,554 |
55 | 7,31,71,800 | 2 | 1,21,22,029 | 2,52,69,255 | 4,79,02,525 |
55 | 7,31,71,800 | 3 | 1,14,92,036 | 3,67,61,291 | 3,64,10,489 |
55 | 7,31,71,800 | 4 | 4,54,06,767 | 8,21,68,058 | -89,96,278 |
55 | 7,31,71,800 | 5 | -3,82,01,819 | 4,39,66,239 | 2,92,05,541 |
55 | 7,31,71,800 | 6 | 1,22,82,439 | 5,62,48,678 | 1,69,23,102 |
55 | 7,31,71,800 | 7 | 93,34,788 | 6,55,83,466 | 75,88,314 |
55 | 7,31,71,800 | 8 | 60,53,224 | 7,16,36,690 | 15,35,090 |
55 | 7,31,71,800 | 9 | 1,12,63,904 | 8,29,00,594 | -97,28,814 |
55 | 7,31,71,800 | 10 | 1,11,88,818 | 9,40,89,412 | -2,09,17,632 |
55 | 7,31,71,800 | 11 | 1,24,16,766 | 10,65,06,178 | -3,33,34,398 |
55 | 7,31,71,800 | 12 | 1,27,96,934 | 11,93,03,112 | -4,61,31,332 |
55 | 7,31,71,800 | 13 | 1,52,30,172 | 13,45,33,284 | -6,13,61,504 |
55 | 7,31,71,800 | 14 | 1,34,41,114 | 14,79,74,398 | -7,48,02,618 |
55 | 7,31,71,800 | 15 | 2,88,42,301 | 17,68,16,699 | -10,36,44,919 |
55 | 7,31,71,800 | 16 | 1,48,54,792 | 19,16,71,491 | -11,84,99,711 |
55 | 7,31,71,800 | 17 | 1,48,31,597 | 20,65,03,088 | -13,33,31,308 |
55 | 7,31,71,800 | 18 | 1,66,38,013 | 22,31,41,101 | -14,99,69,321 |
55 | 7,31,71,800 | 19 | 2,30,03,774 | 24,61,44,875 | -17,29,73,095 |
55 | 7,31,71,800 | 20 | 1,85,93,855 | 26,47,38,730 | -19,15,66,950 |
55 | 7,31,71,800 | 21 | 1,37,09,022 | 27,84,47,752 | -20,52,75,972 |
55 | 7,31,71,800 | 22 | 1,68,95,331 | 29,53,43,083 | -22,21,71,303 |
55 | 7,31,71,800 | 23 | 2,32,59,168 | 31,86,02,251 | -24,54,30,471 |
55 | 7,31,71,800 | 24 | 1,68,16,346 | 33,54,18,597 | -26,22,46,817 |
55 | 7,31,71,800 | 25 | 1,38,07,580 | 34,92,26,177 | -27,60,54,397 |
55 | 7,31,71,800 | 26 | 89,06,720 | 35,81,32,897 | -28,49,61,117 |
55 | 7,31,71,800 | 27 | 4,32,628 | 35,85,65,525 | -28,53,93,745 |
55 | 7,31,71,800 | 28 | 3,63,61,969 | 39,49,27,494 | -32,17,55,714 |
55 | 7,31,71,800 | 29 | -55,20,020 | 38,94,07,474 | -31,62,35,694 |
55 | 7,31,71,800 | 30 | 3,16,54,103 | 42,10,61,577 | -34,78,89,797 |
55 | 7,31,71,800 | 31 | 2,74,69,067 | 44,85,30,644 | -37,53,58,864 |
55 | 7,31,71,800 | 32 | 1,61,60,116 | 46,46,90,760 | -39,15,18,980 |
55 | 7,31,71,800 | 33 | 1,34,76,358 | 47,81,67,118 | -40,49,95,338 |
55 | 7,31,71,800 | 34 | 1,25,20,100 | 49,06,87,218 | -41,75,15,438 |
55 | 7,31,71,800 | 35 | 1,49,68,698 | 50,56,55,916 | -43,24,84,136 |
55 | 7,31,71,800 | 36 | 2,98,47,415 | 53,55,03,331 | -46,23,31,551 |
55 | 7,31,71,800 | 37 | 1,11,17,585 | 54,66,20,916 | -47,34,49,136 |
55 | 7,31,71,800 | 38 | 2,38,21,255 | 57,04,42,171 | -49,72,70,391 |
55 | 7,31,71,800 | 39 | -2,08,97,657 | 54,95,44,514 | -47,63,72,734 |
55 | 7,31,71,800 | 40 | 66,03,256 | 55,61,47,770 | -48,29,75,990 |
55 | 7,31,71,800 | 41 | 1,56,04,988 | 57,17,52,758 | -49,85,80,978 |
55 | 7,31,71,800 | 42 | 1,00,27,702 | 58,17,80,460 | -50,86,08,680 |
55 | 7,31,71,800 | 43 | -18,75,317 | 57,99,05,143 | -50,67,33,363 |
55 | 7,31,71,800 | 44 | -8,49,58,455 | 49,49,46,688 | -42,17,74,908 |
55 | 7,31,71,800 | 45 | 11,51,19,611 | 61,00,66,299 | -53,68,94,519 |
55 | 7,31,71,800 | 46 | 4,10,82,279 | 65,11,48,578 | -57,79,76,798 |
55 | 7,31,71,800 | 47 | -33,88,419 | 64,77,60,159 | -57,45,88,379 |
55 | 7,31,71,800 | 48 | 1,68,14,297 | 66,45,74,456 | -59,14,02,676 |
55 | 7,31,71,800 | 49 | 4,33,92,372 | 70,79,66,828 | -63,47,95,048 |
55 | 7,31,71,800 | 50 | 1,01,55,011 | 71,81,21,839 | -64,49,50,059 |
55 | 7,31,71,800 | 51 | 1,49,98,245 | 73,31,20,084 | -65,99,48,304 |
55 | 7,31,71,800 | 52 | 2,91,62,685 | 76,22,82,769 | -68,91,10,989 |
Assumption : Need unique key for this data. I am assumin that combinatio of code and Week is unique.
Using the data which you shared, please create this measure.
A correction is needed, please check the measure above. I forgot to added filter on code.
My Apology.
Please check, this is a measure
User | Count |
---|---|
57 | |
21 | |
19 | |
18 | |
16 |
User | Count |
---|---|
85 | |
80 | |
52 | |
37 | |
22 |