Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RonaldvdH
Post Patron
Post Patron

Line graph question

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)

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @RonaldvdH 

 

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

f1.png

 

You may create a measure as below.

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

 

Result:

f2.png 

 

Best Regards

Allan

 

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

View solution in original post

8 REPLIES 8
RonaldvdH
Post Patron
Post Patron

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

@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
Community Support
Community Support

Hi, @RonaldvdH 

 

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

f1.png

 

You may create a measure as below.

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

 

Result:

f2.png 

 

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
Post Patron
Post Patron

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
Resolver III
Resolver III

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

 

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
Super User
Super User

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

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.