cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

## Cumulative line chart

Hi all,

I'm trying to built a cumulative line chart for gwp by date but up to now I haven't made it

I use this measure

measure =
var maxdate=MAX('ΝΒ_pivot_CY'[date])
RETURN
CALCULATE(
SUM('ΝΒ_pivot_CY'[gwp]),
'ΝΒ_pivot_CY'[date]<=maxdate,
ALL('ΝΒ_pivot_CY'[date]))

However I get a chart like this

I also tried the quick measure in pwoer bi for running total but no luck

gwp running total in date =
CALCULATE(
SUM('ΝΒ_pivot_CY'[gwp]),
FILTER(
ALLSELECTED('ΝΒ_pivot_CY'[date]),
ISONORAFTER('ΝΒ_pivot_CY'[date], MIN('ΝΒ_pivot_CY'[date]), ASC)
)
)

My data table looks like this. It has repeating date values so I don't know if this

causes the problem

3 ACCEPTED SOLUTIONS
Super User

Most of the time this is caused by a hidden sort column that you forgot to include.

Try

measure =
var maxdate=MAX('ΝΒ_pivot_CY'[date])
RETURN
CALCULATE(
SUM('ΝΒ_pivot_CY'[gwp]),
ALLSELECTED('ΝΒ_pivot_CY').
'ΝΒ_pivot_CY'[date]<=maxdate
)
Super User

@ObdidianMagne Yeah, why you shouldn't use CALCULATE or really any of the default quick measures. There are better ways. Better Running Total - Microsoft Fabric Community.

And the main video you need.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Super User

Hi,

Create a Calendar table with calculated column formulas for Year, Quarter, Month name and Month number.  Sort the Month name column by the Month number.  Create a relationship (many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table.  To your line chart visual, drag Year, Quarter and Month name columns from the Calendar Table.  Write these measures

Total = SUM('ΝΒ_pivot_CY'[gwp])

Running total = calculate([Total],datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date])))

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
3 REPLIES 3
Super User

Hi,

Create a Calendar table with calculated column formulas for Year, Quarter, Month name and Month number.  Sort the Month name column by the Month number.  Create a relationship (many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table.  To your line chart visual, drag Year, Quarter and Month name columns from the Calendar Table.  Write these measures

Total = SUM('ΝΒ_pivot_CY'[gwp])

Running total = calculate([Total],datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date])))

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

@ObdidianMagne Yeah, why you shouldn't use CALCULATE or really any of the default quick measures. There are better ways. Better Running Total - Microsoft Fabric Community.

And the main video you need.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Super User

Most of the time this is caused by a hidden sort column that you forgot to include.

Try

measure =
var maxdate=MAX('ΝΒ_pivot_CY'[date])
RETURN
CALCULATE(
SUM('ΝΒ_pivot_CY'[gwp]),
ALLSELECTED('ΝΒ_pivot_CY').
'ΝΒ_pivot_CY'[date]<=maxdate
)

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors