Distinct Count based on Condition

[s_fs]

I have created a table visual there I have a column which is numeric and it is weekyear like 202501,202502,202503 ... Then I have a another columns called in-hand goods and demand  for the respective weekyear. Now I want to create measure how much I have covered. This measure says that my present weekyear,let say 202501 in-hand goods are 4000 and demand for 202502 is 2000 means I can survive next week with my inhand goods and then we will check for next week 202503 demand which is 1500 . So, we still can survive 202503 week also, for 202504 demand is 1000 . Now comes the point where you exhausted with your inhand stock which is 2000+1500+1000 >= 4000.now I want to count the distinct weekyear which is 3  here. Also the safe week means 202503 is the week where we are safe with our goods based on the demand.so,i need to get the sum means 2000+1500 =3500 also. Here 2 variable I need to achieve,Let me know how can I achieve this in dax.

[v-pgoloju]

Hi @s_fs,

Just a gentle reminder — has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.

This not only closes the loop on your query but also helps others in the community solve similar issues faster.

Thank you for your time and feedback!

Best,

Prasanna Kumar

[v-pgoloju]

Hi @s_fs,

We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.

If the issue is resolved, we’d appreciate it if you could mark the helpful reply as Accepted Solution — it helps others who might face a similar issue.

Warm regards,

Prasanna Kumar

[v-pgoloju]

Hi @s_fs,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful — this will benefit others in the community as well.

Best regards,

Prasanna Kumar

[bhanu_gautam]

@s_fs First create a measure

Measure to count the distinct weekyears you can cover:

dax
Covered Weeks Count =
VAR CurrentWeek = MAX('Table'[WeekYear])
VAR InHandGoods = MAX('Table'[InHandGoods])
VAR DemandTable =
FILTER(
'Table',
'Table'[WeekYear] > CurrentWeek
)
VAR CumulativeDemand =
SUMX(
DemandTable,
CALCULATE(
SUM('Table'[Demand]),
FILTER(
'Table',
'Table'[WeekYear] <= EARLIER('Table'[WeekYear])
)
)
)
VAR CoveredWeeks =
COUNTROWS(
FILTER(
DemandTable,
CumulativeDemand <= InHandGoods
)
)
RETURN
CoveredWeeks

Measure to calculate the sum of the demand for the weeks you can cover:

dax
Covered Demand Sum =
VAR CurrentWeek = MAX('Table'[WeekYear])
VAR InHandGoods = MAX('Table'[InHandGoods])
VAR DemandTable =
FILTER(
'Table',
'Table'[WeekYear] > CurrentWeek
)
VAR CumulativeDemand =
SUMX(
DemandTable,
CALCULATE(
SUM('Table'[Demand]),
FILTER(
'Table',
'Table'[WeekYear] <= EARLIER('Table'[WeekYear])
)
)
)
VAR CoveredDemand =
SUMX(
FILTER(
DemandTable,
CumulativeDemand <= InHandGoods
),
'Table'[Demand]
)
RETURN
CoveredDemand

[s_fs]

Hi @bhanu_gautam , 

Thanks for the query, my in hand goods and demand are measures. I believe MAX won't work for measures right??? 

[bhanu_gautam]

@s_fs , use this it reference measure directly

dax
Covered Weeks Count =
VAR CurrentWeek = MAX('Table'[WeekYear])
VAR InHandGoods = [InHandGoods] -- Reference the measure directly
VAR DemandTable =
FILTER(
'Table',
'Table'[WeekYear] > CurrentWeek
)
VAR CumulativeDemand =
SUMX(
DemandTable,
CALCULATE(
[Demand], -- Reference the measure directly
FILTER(
'Table',
'Table'[WeekYear] <= EARLIER('Table'[WeekYear])
)
)
)
VAR CoveredWeeks =
COUNTROWS(
FILTER(
DemandTable,
CumulativeDemand <= InHandGoods
)
)
RETURN
CoveredWeeks

dax
Covered Demand Sum =
VAR CurrentWeek = MAX('Table'[WeekYear])
VAR InHandGoods = [InHandGoods] -- Reference the measure directly
VAR DemandTable =
FILTER(
'Table',
'Table'[WeekYear] > CurrentWeek
)
VAR CumulativeDemand =
SUMX(
DemandTable,
CALCULATE(
[Demand], -- Reference the measure directly
FILTER(
'Table',
'Table'[WeekYear] <= EARLIER('Table'[WeekYear])
)
)
)
VAR CoveredDemand =
SUMX(
FILTER(
DemandTable,
CumulativeDemand <= InHandGoods
),
[Demand] -- Reference the measure directly
)
RETURN
CoveredDemand