Forum Discussion

Velocity's avatar
Velocity
Icon for Helper III rankHelper III
6 years ago
Solved

Measure is not considering all months

Hi

I am unable to understand why the cumulative percentage is dipping. It seems whenever there is no data for a week, it skips the month's value. PBIX file is available at:

https://1drv.ms/u/s!AlAhec3copzOiApMY0RIl-8Tmjzj?e=51fXB9

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Velocity,

    I'd like to suggest you create a calendar table with full date ranges(notice: calendar not has a relationship to the original table), then you can use it as the axis and extract current calendar date to rolling calculate on your table records.

    Cumulative RMY responses =
VAR currDate =
    MAX ( Calendar[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( ResponseMaster ),
        FILTER (
            ALLSELECTED ( ResponseMaster ),
            ResponseMaster[Week Num RMY] <= currDate
        )
    )

    In addition, I do not so recommend your to use allexcept function. It also contains 'all' function that will ignore the filter effects.

    Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT  

    Regards,

    Xiaoxin Sheng

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Velocity , can you explain the issue. Not very clear in file.

     

    if you need more help make me @

    Appreciate your Kudos.

    • Velocity's avatar
      Velocity
      Icon for Helper III rankHelper III
      6 years ago

      amitchandak 

      Actually, since the chart is supposed to plot cumulative value, it should never be decreasing. If you notice value for week 14 and 15 is dipping because there is no data for month '09/30/2019' (please refere table) and hence the cumutive figure (Cumulative RMY responses) of that month is not getting added. 

       

      Hope i have been able to explain the issue.

       

      Thanks.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Velocity,

        I'd like to suggest you create a calendar table with full date ranges(notice: calendar not has a relationship to the original table), then you can use it as the axis and extract current calendar date to rolling calculate on your table records.

        Cumulative RMY responses =
VAR currDate =
    MAX ( Calendar[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( ResponseMaster ),
        FILTER (
            ALLSELECTED ( ResponseMaster ),
            ResponseMaster[Week Num RMY] <= currDate
        )
    )

        In addition, I do not so recommend your to use allexcept function. It also contains 'all' function that will ignore the filter effects.

        Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT  

        Regards,

        Xiaoxin Sheng