Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calendar Running Total line graph

Hi,    I have no clue why i get this result. It's very frustrated..   I tried to show running total progress by week. i used this query for running total  and this is the relationship ...
  • v-alq-msft's avatar
    6 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario.

    Table:

     

    Calendar(a calculated table):

     

    Calendar = CALENDAR(DATE(2020,1,1),DATE(2020,3,31))

     

     

    You may create a calculated column as below.

     

    WeekNum = WEEKNUM('Calendar'[Date])

     

     

    There is a one-to-one relationship between two tables.

     

    Then you can create a measure as below.

     

    Count Running total = 
    var _weeknum = SELECTEDVALUE('Calendar'[WeekNum])
    var _date = SELECTEDVALUE('Calendar'[Date])
    return
    IF(
        ISINSCOPE('Calendar'[Date]),
        CALCULATE(
            COUNT('Table'[Date]),
            FILTER(
                ALLSELECTED('Calendar'),
                'Calendar'[Date]<=_date
            )
        ),
        CALCULATE(
            COUNT('Table'[Date]),
            FILTER(
                ALLSELECTED('Calendar'),
                'Calendar'[WeekNum]<=_weeknum
            )
        )
    )

     

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.