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

this is the result graph.. not sure why i get the blank although i don't have blank in the database..!

 

any advice would be much appreciated.!

 

thanks,

CL

 

Need Help
Show and Tell
  • v-alq-msft's avatar
    v-alq-msft
    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.

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous ,

    Try with a date calendar. like

    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,max(dateadd(date[date]),-1,year))))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      hello amitchandak

       

      thanks for your reply! 

      can you tell me a bit more detail? i am pretty new in PBI.. i am not sure where i need those codes? 

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    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.