Forum Discussion

s_fs's avatar
s_fs
Frequent Visitor
1 year ago

Distinct Count based on Condition

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.

6 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    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's avatar
      s_fs
      Frequent Visitor
      1 year ago

      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 avatar
        bhanu_gautam
        Super User
        1 year ago

        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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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