cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Anonymous
Not applicable

## Calculation of Sales between two dates, then calculation of Sales one week ago since this date

Dears,

My idea is to calculate Sales between two dates in my shops, when special offers for clients started and ended, then I would like to calculate Sales one week ago since this date.

For example, special offers for clients started 15/05/2021 and ended 19/05/2021. I calculated Sales for these periods, then I would like to calculate Sales in these periods between 08/05/2021 and 12-05-2021.

There are columns in my table:

A column with Sales [Sales], a column with dates [Date] from 1/01/2021 to 26/12/2021, a column with special offers [Special offers] and a column with dates [DateOfPromotion] for these special offers (the lenght of the promotion).

I calculated Sales

Sales = sum(Table[Sales])

Therefore, I calculated when my promotion started and ended:

Start Promotion= min(Table[DateOfPromotion])
End Promotion= max(Table[DateOfPromotion])

Then, I calculated Sales between these dates:

Sales in this promotion= calculate(Sales,DATESBETWEEN([Table[Date],[Start Promotion],[End Promotion]))
Next, I calculated Sales between these dates seven days ago
First, I calculated date seven days ago from the promotion date:
Start Promotion one week ago= min(Table[DateOfPromotion]) - 7
End Promotion one week ago= MAX(Table[DateOfPromotion]) - 7
Then, I calculated Sales between these dates:
Sales one week ago= calculate(Sales,DATESBETWEEN([Table[Date],[Start Promotion one week before],[End Promotion one week before]))
Finally, I put all measurements in one table, because I would like to compare Sales in this promotion, then Sales one week ago (when it did not take place):

As you can see my idea does not work.
I tried to use Calendar Table, but I obtained the same results.
I was wondering what I did wrongly.

Would you be so kind as to give me your advice how to deal with it properly?
I would apperciate it if you could write your measurement (DAX) with an obvious explanation.

I am looking forward to receiving your ideas.

L.

2 REPLIES 2
Super User

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Anonymous
Not applicable

I shared this table output:

Is it enough for you ?

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors