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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
jochem_werff
Frequent Visitor

Cumalative line chart with different years

Dear community, i want to make a line chart like this: 
VoorbeeldPowerBI.JPGIts just a example how I want it. Its a Line chart with the cumalative amount of every year. I want on the X-axis every month. On the Y-axis the amount. And I want lines of the last 3 year, so from 2024, 2023 and 2022. 






I cant get it done in PowerBI I tried a lot of measures but no one works for me. 
Last Measure I tried is this one:  

I already made a Calandertable. So Basicly what I want is a line chart with different lines of years cumalative. And I Mean of every year the line needs to begin at zero
Powerbi2.pngI hope some one can help me! Thank you so much

1 ACCEPTED SOLUTION
talespin
Solution Sage
Solution Sage

hi @jochem_werff 

 

I am using Contoso database to simulate your case. My Date table has all dates present in sales table(Order Date).

Date table is linked to sales table one to many(Single). Sharing explanation below.

 

Step1: Setup data model as stated above.

Step2: In date table sort the Month name column with month no, so that months appear sorted on your visual.

Step3: Create this measure.

 

Measure

------------

Sales Y0 =
//This will get the current selected month in visual.
VAR _Month = SELECTEDVALUE('Date'[Month Number]
//I am getting the MAX order date as in my database I only have data till 2020, you can modify the logic as per your requirement. So I am starting with Year 2020 as Year0. 
VAR _Year = YEAR(MAX(Orders[Order Date]))
 
//I am creating start date and end date to do running totals.
VAR _StartDate = DATE(_Year, 1,1)
VAR _EndDate = EOMONTH(DATE(_Year,_Month,1), 0)
 
//This calculates the sales amount for above dates.
RETURN CALCULATE([Sales Amount], REMOVEFILTERS('Date'[Month Short]), 'Date'[Date] >= _StartDate && 'Date'[Date] <= _EndDate)
 
 
//Similarly you can create for as many years you want, keep subtracting 1 to go back one year. Drag drop all the measures into line chat Y-Axis and Month Short on X-Axis.
Sales Yminus1 =
VAR _Month = SELECTEDVALUE('Date'[Month Number])
VAR _Year = YEAR(MAX(Orders[Order Date])) -1
VAR _StartDate = DATE(_Year, 1,1)
VAR _EndDate = EOMONTH(DATE(_Year,_Month,1), 0)

RETURN CALCULATE([Sales Amount], REMOVEFILTERS('Date'[Month Short]), 'Date'[Date] >= _StartDate && 'Date'[Date] <= _EndDate)
 
talespin_0-1708705605659.pngtalespin_1-1708705681073.png

 

 

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

Hi @jochem_werff,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
talespin
Solution Sage
Solution Sage

hi @jochem_werff 

 

I am using Contoso database to simulate your case. My Date table has all dates present in sales table(Order Date).

Date table is linked to sales table one to many(Single). Sharing explanation below.

 

Step1: Setup data model as stated above.

Step2: In date table sort the Month name column with month no, so that months appear sorted on your visual.

Step3: Create this measure.

 

Measure

------------

Sales Y0 =
//This will get the current selected month in visual.
VAR _Month = SELECTEDVALUE('Date'[Month Number]
//I am getting the MAX order date as in my database I only have data till 2020, you can modify the logic as per your requirement. So I am starting with Year 2020 as Year0. 
VAR _Year = YEAR(MAX(Orders[Order Date]))
 
//I am creating start date and end date to do running totals.
VAR _StartDate = DATE(_Year, 1,1)
VAR _EndDate = EOMONTH(DATE(_Year,_Month,1), 0)
 
//This calculates the sales amount for above dates.
RETURN CALCULATE([Sales Amount], REMOVEFILTERS('Date'[Month Short]), 'Date'[Date] >= _StartDate && 'Date'[Date] <= _EndDate)
 
 
//Similarly you can create for as many years you want, keep subtracting 1 to go back one year. Drag drop all the measures into line chat Y-Axis and Month Short on X-Axis.
Sales Yminus1 =
VAR _Month = SELECTEDVALUE('Date'[Month Number])
VAR _Year = YEAR(MAX(Orders[Order Date])) -1
VAR _StartDate = DATE(_Year, 1,1)
VAR _EndDate = EOMONTH(DATE(_Year,_Month,1), 0)

RETURN CALCULATE([Sales Amount], REMOVEFILTERS('Date'[Month Short]), 'Date'[Date] >= _StartDate && 'Date'[Date] <= _EndDate)
 
talespin_0-1708705605659.pngtalespin_1-1708705681073.png

 

 

lbendlin
Super User
Super User

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors