Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.

Reply
it_akasjraj
Frequent Visitor

Sales Between Two Days, Not Date.

Please find screenshot for better understanding,

Screenshot (85).png

 

Issue Is:

I have to calculate the sum of Sales Amount column only for two product i.e "APPS" And "NONAPPS", For Today, For Last 7 Days, For Last 8 To 15 Days, For Last 15 To 30 Days, More Than 30 Days.

Today Means - Sum Of Today i.e 18-04-2022 Sales For APPS And NONAPPS Product category.

Last 7 Days Means - Sum Of Sales Between Yesterday i.e. 17-04-2022 To 7 Days Back i.e. 11-04-2022, For APPS And NONAPPS Product category.

Last 8 To 15 Days Means - Sum Of Sales Between 10-04-2022 To 27-03-2022, For APPS And NONAPPS Product category.

Last 16 To 30 Days Means - Sum Of Sales Between 26-03-2022 To 12-03-2022, For APPS And NONAPPS Product category.

More Than 30 Days Means - Sum Of Sales Happened Before 12-03-2022.

 

Dataset Is Attached.

https://docs.google.com/spreadsheets/d/1_t2I2Cd_26VHEals6KzG107udUY49516/edit?usp=sharing&ouid=11393...

 

Measures Has To Be Individual So Total 5 Measures I Have To Create, Please Help Me Community And Experts.

 

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @it_akasjraj 

 

You can try the following methods. Because I replied to your post on April 21, I subtracted three days from each today date to get the date of your original post on April 18.

Measure:

Today = 
CALCULATE(SUM('Table'[Sales Amount]),FILTER(ALL('Table'),[Product]="APPS"&&[Date]=TODAY()-3))
+CALCULATE(SUM('Table'[Sales Amount]),FILTER(ALL('Table'),[Product]="NON APPS"&&[Date]=TODAY()-3))
Last 7 Days = 
CALCULATE(SUM('Table'[Sales Amount]),FILTER(ALL('Table'),[Product]="APPS"&&[Date]<=TODAY()-3-1&&[Date]>=TODAY()-3-7))
+CALCULATE(SUM('Table'[Sales Amount]),FILTER(ALL('Table'),[Product]="NON APPS"&&[Date]<=TODAY()-3-1&&[Date]>=TODAY()-3-7))
Last 8 To 15 Days = 
CALCULATE(SUM('Table'[Sales Amount]),FILTER('Table',[Product]="APPS"&&[Date]<=TODAY()-3-8&&[Date]>=TODAY()-3-8-14))
+CALCULATE(SUM('Table'[Sales Amount]),FILTER('Table',[Product]="NON APPS"&&[Date]<=TODAY()-3-8&&[Date]>=TODAY()-3-8-14))
Last 16 To 30 Days = 
CALCULATE(SUM('Table'[Sales Amount]),FILTER(ALL('Table'),[Product]="APPS"&&[Date]<=TODAY()-3-23&&[Date]>=TODAY()-3-37))
+CALCULATE(SUM('Table'[Sales Amount]),FILTER(ALL('Table'),[Product]="NON APPS"&&[Date]<=TODAY()-3-23&&[Date]>=TODAY()-3-37))
More Than 30 Days = 
CALCULATE(SUM('Table'[Sales Amount]),FILTER(ALL('Table'),[Product]="APPS"&&[Date]<TODAY()-3-37))
+CALCULATE(SUM('Table'[Sales Amount]),FILTER(ALL('Table'),[Product]="NON APPS"&&[Date]<TODAY()-3-37))

vzhangti_0-1650533431089.png

Is this what you expect? It can be compared with the data you expect.

 

Best Regards,

Community Support Team _Charlotte

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

4 REPLIES 4
Whitewater100
Solution Sage
Solution Sage

Hi:

When you upload the file you will see all three broken out by period.

 

https://drive.google.com/file/d/1p27ItKxLyGYFc0eVol3q0hKfziEC5_9X/view?usp=sharing

 

App & Non-App

App

Non-App

For instance if you choose Last 7 Days:

Whitewater100_0-1650555261276.pngWhitewater100_1-1650555286309.pngWhitewater100_2-1650555323201.png

 

 

Whitewater100
Solution Sage
Solution Sage

Hi:

I added a few days to your dataset to bring to today. There is a Date Table with a Day Index on the attached file. This was used along with a measure selector table to obtain results. Should make it easy for user to select:-)

I hope this helps.

https://drive.google.com/file/d/1p27ItKxLyGYFc0eVol3q0hKfziEC5_9X/view?usp=sharing 

 

Whitewater100_0-1650551456319.png

 

v-zhangti
Community Support
Community Support

Hi, @it_akasjraj 

 

You can try the following methods. Because I replied to your post on April 21, I subtracted three days from each today date to get the date of your original post on April 18.

Measure:

Today = 
CALCULATE(SUM('Table'[Sales Amount]),FILTER(ALL('Table'),[Product]="APPS"&&[Date]=TODAY()-3))
+CALCULATE(SUM('Table'[Sales Amount]),FILTER(ALL('Table'),[Product]="NON APPS"&&[Date]=TODAY()-3))
Last 7 Days = 
CALCULATE(SUM('Table'[Sales Amount]),FILTER(ALL('Table'),[Product]="APPS"&&[Date]<=TODAY()-3-1&&[Date]>=TODAY()-3-7))
+CALCULATE(SUM('Table'[Sales Amount]),FILTER(ALL('Table'),[Product]="NON APPS"&&[Date]<=TODAY()-3-1&&[Date]>=TODAY()-3-7))
Last 8 To 15 Days = 
CALCULATE(SUM('Table'[Sales Amount]),FILTER('Table',[Product]="APPS"&&[Date]<=TODAY()-3-8&&[Date]>=TODAY()-3-8-14))
+CALCULATE(SUM('Table'[Sales Amount]),FILTER('Table',[Product]="NON APPS"&&[Date]<=TODAY()-3-8&&[Date]>=TODAY()-3-8-14))
Last 16 To 30 Days = 
CALCULATE(SUM('Table'[Sales Amount]),FILTER(ALL('Table'),[Product]="APPS"&&[Date]<=TODAY()-3-23&&[Date]>=TODAY()-3-37))
+CALCULATE(SUM('Table'[Sales Amount]),FILTER(ALL('Table'),[Product]="NON APPS"&&[Date]<=TODAY()-3-23&&[Date]>=TODAY()-3-37))
More Than 30 Days = 
CALCULATE(SUM('Table'[Sales Amount]),FILTER(ALL('Table'),[Product]="APPS"&&[Date]<TODAY()-3-37))
+CALCULATE(SUM('Table'[Sales Amount]),FILTER(ALL('Table'),[Product]="NON APPS"&&[Date]<TODAY()-3-37))

vzhangti_0-1650533431089.png

Is this what you expect? It can be compared with the data you expect.

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

tamerj1
Super User
Super User

Hi @it_akasjraj 

no need to create 5 measures. 
You can create a disconnected table that contains the Day values:

Selection                  Value

Today                    0

Last 7 Days            7

etc. 

you can do it in excel or any way makes comfortable. 
Use this table to create a slicer or drag in to your matix columns then modify your measure to adapt for SELECTEDVALUE instead of hard coding a fixed number for each measure

 

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

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

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

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