Forum Discussion

jochem_werff's avatar
jochem_werff
Frequent Visitor
2 years ago
Solved

Cumalative line chart with different years

Dear community, i want to make a line chart like this: 
Its 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
I hope some one can help me! Thank you so much

  • talespin's avatar
    talespin
    2 years ago

    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)
     

     

     

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    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-Forum/ba-p/963216

    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/1447523

  • talespin's avatar
    talespin
    Icon for Solution Sage rankSolution Sage
    2 years ago

    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)
     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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