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

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

Reply
ObdidianMagne
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
 
ObdidianMagne_0-1713020311070.png

 

 

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

 

ObdidianMagne_1-1713020722015.png

 

Could you help please?

 

 

3 ACCEPTED SOLUTIONS
lbendlin
Super User
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
    )

View solution in original post

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

 

Here's a playlist: https://www.youtube.com/playlist?list=PLoibEIc7heYNZbnY4CTCtg2tjEcMh8WbZ

 

And the main video you need.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
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.

 

Here's a playlist: https://www.youtube.com/playlist?list=PLoibEIc7heYNZbnY4CTCtg2tjEcMh8WbZ

 

And the main video you need.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
lbendlin
Super User
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
    )

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.