topic Re: Dax formula for summing up the days with stop condition. in DAX Commands and Tips

Dax formula for summing up the days with stop condition.

Anonymous — Thu, 21 Mar 2024 10:02:28 GMT

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

Re: Dax formula for summing up the days with stop condition.

talespin — Thu, 21 Mar 2024 10:55:38 GMT

hi Anonymous

Please check, this is a measure

DaysTotal =

VAR _Week = CALCULATE( MIN(Week[Week ]), REMOVEFILTERS(), Week[ Days ] <> 7)

RETURN CALCULATE( SUM(Week[ Days ]), REMOVEFILTERS(), Week[Week ] <= _Week)

Re: Dax formula for summing up the days with stop condition.

Anonymous — Thu, 21 Mar 2024 15:44:02 GMT

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.

Re: Dax formula for summing up the days with stop condition.

talespin — Fri, 22 Mar 2024 03:58:09 GMT

hi Anonymous

What is in the measure week[days] ?

Re: Dax formula for summing up the days with stop condition.

Anonymous — Fri, 22 Mar 2024 06:27:35 GMT

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-

Revenue days =

Var Total_Inventory=SUM(data[Inventory])

VAR Total_Revenue=SUM(data[Revenue])

Var Total_cumulative_Revenue=SUM(data[Cumulative Revenue])

Var Remaining_inventory=Total_Inventory-Total_cumulative_Revenue

Var Revenue_Week = if(Remaining_inventory>=0,7,0)

Var additional_days= IF(AND(Total_cumulative_Revenue>Total_Inventory,Total_Revenue+Remaining_inventory>0),((Total_Revenue+Remaining_inventory)/Total_Revenue)*7,0)

RETURN

if(Total_Inventory<=0,0,Revenue_Week+additional_days)

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

Re: Dax formula for summing up the days with stop condition.

talespin — Sat, 23 Mar 2024 14:58:18 GMT

hi Anonymous

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.

DaysTotal =

VAR _Code = SELECTEDVALUE(Test3[Code])

VAR _SummTbl =

ADDCOLUMNS(

SUMMARIZE(ALL(Test3[Code], Test3[Week]), Test3[Code], Test3[Week]),

"@RevenueDays",

VAR _code = [Code]

VAR _Week = [Week]

RETURN CALCULATE([Revenue days], REMOVEFILTERS(), Test3[Code] = _code && Test3[Week] = _Week)

)

VAR _Week = MINX( FILTER(_SummTbl, [Code] = _Code && [@RevenueDays] <> 7), [Week])

RETURN SUMX( FILTER(_SummTbl, [Code] = _Code && [Week] <= _Week), [@RevenueDays])

Re: Dax formula for summing up the days with stop condition.

Anonymous — Sat, 23 Mar 2024 10:11:45 GMT

Thanks talespin really appreciate your help

Re: Dax formula for summing up the days with stop condition.

talespin — Sat, 23 Mar 2024 14:59:36 GMT

hi Anonymous

A correction is needed, please check the measure above. I forgot to added filter on code.

My Apology.