Calendar Running Total line graph

[Anonymous]

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

[v-alq-msft · Accepted solution]

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.

[amitchandak]

@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]

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?