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
Jacobcw99E
New Member

Convert cumulative to growth values for multiple categories

I have a value of cumulative sales each week for different sites. How do I use this to plot the sales each week for sites? ie. the difference in cumulative sales each week for these.

1 ACCEPTED SOLUTION

Hi @Jacobcw99E ,

Now, Power BI doesn't provide the previous week dax funtion, but you could achieve it with other function.

Using the OFFSET Function:
The OFFSET function in DAX (Data Analysis Expressions) allows us to retrieve values relative to the current position. We’ll use it to get the sales from the previous week.
Here’s how you can create a measure for previous week sales:

Sales Previous Week = CALCULATE(
    [Sales Amount],
    OFFSET(
        -1,
        ORDERBY('Date'[Year Week], ASC)
    )
)

This measure calculates the sales amount from the last row (previous week) compared to the current one1.

 

For more details, you could read related document: OFFSET - DAX Guide

 

Best regards,
Community Support Team_Binbin Yu
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

3 REPLIES 3
v-binbinyu-msft
Community Support
Community Support

Hi @Jacobcw99E ,

Please try below steps:

1. Calculate Weekly Sales Growth: First, you need to calculate the difference in cumulative sales between each week to get the weekly sales growth. You can achieve this by creating a new measure in Power BI using DAX (Data Analysis Expressions). Here's an example DAX formula you can use:

Weekly Sales Growth = 
    CALCULATE (
        SUM ( Sales[Cumulative Sales] ),
        Sales[Week]
    ) - CALCULATE (
        SUM ( Sales[Cumulative Sales] ),
        PREVIOUSMONTH ( Sales[Week] )
    )

Replace 'Sales[Cumulative Sales]' with your cumulative sales column and 'Sales[Week]' with your week column. This formula calculates the difference in cumulative sales between the current week and the previous week, giving you the weekly sales growth.

 

2. Plot the Data: Once you have the weekly sales growth calculated, you can plot this data in Power BI. A line chart or a column chart could be effective for visualizing weekly sales growth across different sites. To do this:
- Go to the "Visualizations" pane and select the chart type you prefer (e.g., Line chart).
- Drag your "Week" field to the Axis area.
- Drag your newly created "Weekly Sales Growth" measure to the Values area.
- If you want to analyze by site, drag your "Site" field to the Legend area.

This approach will allow you to visualize the weekly sales growth for each site over time, helping you identify trends, patterns, and outliers in sales performance.

 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-binbinyu-msft the graph doesn't look quite right surely I need it to be previous week not previous month (but that function doesn't exist)?

Hi @Jacobcw99E ,

Now, Power BI doesn't provide the previous week dax funtion, but you could achieve it with other function.

Using the OFFSET Function:
The OFFSET function in DAX (Data Analysis Expressions) allows us to retrieve values relative to the current position. We’ll use it to get the sales from the previous week.
Here’s how you can create a measure for previous week sales:

Sales Previous Week = CALCULATE(
    [Sales Amount],
    OFFSET(
        -1,
        ORDERBY('Date'[Year Week], ASC)
    )
)

This measure calculates the sales amount from the last row (previous week) compared to the current one1.

 

For more details, you could read related document: OFFSET - DAX Guide

 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.