Line graph question

[RonaldvdH]

Goodmorning guys, i need your help with a simple question

I want a line graph that displays the cumulative value of a certain value

We register on a daily basis the number of new contracts that are entered and i want that do be displayed in a line graph.

So far so good but that line is based on a value per day instead of a cumulativ line

What i need is a cumulative line per week that grows.

wk 1      200

wk 2      150

wk 3      160

wk 4      220

But my line should show these values

wk 1      200

wk 2      350 (200+150)

wk 3      510 (200+150+160)

wk 4      730 (200+150+160+220)

So basically i need a measure that sums the values based on a week and at the previouos week(s)

[RonaldvdH]

i had a date table in my report (totally forgot about it) and now it works based on your help

[RonaldvdH]

@v-alq-msft I was to quick in my response because i used your pbix file but how did you calculate the value ? it looks like you used a tifferent measure to calculate the total amount of dates in a given week right ?

[v-alq-msft · Accepted solution]

Hi, @RonaldvdH 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

You may create a measure as below.

Result = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALL('Table'),
        [YearWeek]<=MAX('Table'[YearWeek])
    )
)

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.

[RonaldvdH]

What i did is the following, i have a table with adressen and a date.

I use a formula to calculate the date into a week by using this formula

if [actief aangemeld] = null then ""
else
Text.From(Date.Year([actief aangemeld])) & "-" & Text.PadStart(Text.From(Date.WeekOfYear([actief aangemeld])), 2, "0")

And then the formula doesn't work because it's a string but i always use the format YYYY-WW

[P_D_G]

Hi,

have you tried adding a Quick Measure - Running total? Usually it works fine.

Or you can create a measure on your own as well:

calculate( SUM( YourColumn ), Filter (all ( YourTable[DateColumn] ), YourTable[DateColumn] <= MAX(YourTable[DateColumn]) )

Or you can do the same in Power Query:

https://www.youtube.com/watch?v=uX3_dnb5on0

[RonaldvdH]

Your formula doesn't seem to work because i used a formula to convert my date to a week using this formula in the advanced editor:

if [actief aangemeld] = null then ""
else
Text.From(Date.Year([actief aangemeld])) & "-" & Text.PadStart(Text.From(Date.WeekOfYear([actief aangemeld])), 2, "0")

The result is a string and therefor it doesn't work i think

[amitchandak]

@RonaldvdH , if you are using a date table and week is part of that then it will give cumulative with dates

example

Cumm Sales = CALCULATE(SUM(Table[value]),filter(allselected(Date),Date[Date] <=max(Date[Date])))

or

Cumm Sales = CALCULATE(SUM(Table[value]),filter(allselected(Table),Table[Date] <=max(Table[Date])))

else create a week rank in table and use that

Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYMM format

Cumm Sales = CALCULATE(SUM(Table[value]),filter(allselected(Date),Date[Week Rank] <=max(Date[Week Rank])))

[RonaldvdH]

I dont have a date table so is there an option to work with the data i do have ?

I have a table with dates in them and i use a formula to convert (to a string) the dates into a week number format YYYY-MM