Convert cumulative to growth values for multiple categories

[Jacobcw99E]

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.

[Anonymous]

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.

[Jacobcw99E]

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

[Anonymous · 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.