cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors