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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
cborde3
Frequent Visitor

Running Totals for Two Years

Hello,
I'm trying to display the running total of errors for two years in a line graph so that they can be compared. I need  a visual similar to this:
 
cborde3_0-1679095769391.png
 
My calculation for 2023 is:
 
2023 = TOTALYTD(SUM('PA Switching Errors'[Distinct]),'Calendar'[Date])
 
2023's calculation tracks correctly.
 
For 2022, I'm trying:
 
2022 Cumulative Errors = CALCULATE(
        SUM('PA Switching Errors'[Distinct]),
            FILTER('PA Switching Errors',
                    'PA Switching Errors'[Year] = "2022"))
 
2022 does not track correctly.  These two result in the following graph:
 
cborde3_0-1679097363997.png

 

I can't get 2022 to give me a cumulative line.
 
Also, when placed in a table, the 2022 cumulative calculation gives this, which is clearly not cumulative:
 
cborde3_0-1679097549731.png

 

 
Thanks in advance for any assistance.
 

 

2 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Hi @cborde3 ,

You can update the formula of measure as below to get the culmulative values, please find the details in the attachment.. If you want to get the value for the specific year, you can add the condition: YEAR(_seldate)=2022 to get it.

Sample 2022 Cumulative Errors = 
VAR _seldate =
    SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        SUM ( Sheet1[Distinct] ),
        FILTER ( ALLSELECTED ( Sheet1 ), 'Sheet1'[Date] <= _seldate )
    )

yingyinr_0-1679361627029.png

Best Regards

Community Support Team _ Rena
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

6 REPLIES 6
cborde3
Frequent Visitor

Thank you all for your support!

Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

Share the download link of the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yiruan-msft
Community Support
Community Support

Hi  @cborde3 ,

Please update the formula of measure [2022 Cumulative Errors] as below and check if it can return the expected result...

2022 Cumulative Errors =
VAR _seldate =
    SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        SUM ( 'PA Switching Errors'[Distinct] ),
        FILTER (
            ALLSELECTED ( 'PA Switching Errors' ),
            'PA Switching Errors'[Year] = "2022"
                && 'PA Switching Errors'[Incident Date] <= _seldate
        )
    )

 

If the above one can't help you, please provide some raw data in your table (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

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

Hello,

 

I've copied a sample file to drop box.  The link is below.

 

https://www.dropbox.com/s/h5p40g7upwqoi4b/Sample%20Sheet%20%281%29.pbix?dl=0

Hi @cborde3 ,

You can update the formula of measure as below to get the culmulative values, please find the details in the attachment.. If you want to get the value for the specific year, you can add the condition: YEAR(_seldate)=2022 to get it.

Sample 2022 Cumulative Errors = 
VAR _seldate =
    SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        SUM ( Sheet1[Distinct] ),
        FILTER ( ALLSELECTED ( Sheet1 ), 'Sheet1'[Date] <= _seldate )
    )

yingyinr_0-1679361627029.png

Best Regards

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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