cancel
Showing results 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.

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
Community Support

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.

3 REPLIES 3
Community Support

Hi @Jacobcw99E ,

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.

New Member

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)?

Community Support

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.

Announcements

#### 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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors