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

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
S_JB
Resolver III
Resolver III

Comparison based on Date Added

Hi All,

 

I'm trying to build a report which compares the sales which have been added on to the system today versus a selected date within the last 60 days. The variables included are customer, sales person, date added on to system, shipping date and item no. The aim is for the report to show the sales amount on the day selected compared with today's sales amount. The variables mentioned are included in a table to show what is driving the sales movement.

 

Originally, I tried to achieve this by building a measure which will take the sales amount of the date selected in the slicer and then having a measure with today's sales:

 

Date Selected Sales:

Sales - (Selected) = CALCULATE
(CALCULATE
(sum('Query1'[GBP Net]),
FILTER(ALLSELECTED('Query1'[Date Added].[Date]),
'Query1'[Date Added].[Date]<=MAX('Query1'[Date Added].[Date]) )) ,
'Query1',
'Query1'[Type] <> "Credited",
Query1[Type] <> "BSO D/D")
 
Today's Sales:
 
Sales (Today) =
CALCULATE(
SUM('Query1'[GBP Net]),
'Query1'[Type]<>"BSO D/D",
'Query1'[Type]<>"Credited",
'Query1'[Date Added] = TODAY()
)
 
The totals figure matched with this approach but the data within the table which splits sales by customer and item no does not match. This is due to the date added slicer dictating the data within the table. So if we did not sell an item on the day selected, it does not appear in the table, but the total remains the same.
 
I have tried to get around this by building two cross joins. One for date added and customer and another for date added and item no. But there would need to be another cross join performed on these, which would result in millions of rows.
 
Is there a way I can alter my approach in Power BI so that I can compare the date added date against a selected date without losing any data in the table? Ideally I will not need to use a cross join and can achive the required outcome in Power BI.
 
Hopefully this makes sense and any assistance is much appreciated.
 
Thank you! 
 
1 ACCEPTED SOLUTION
S_JB
Resolver III
Resolver III

To resolve this issue I have altered the cross join. I have taken distinct date added and a separate list which is distinct customer, sales person and item no. The cross join is then performed on these two lists with a limitation to only include instances which do not already exist in the dataset.

 

This means if I select a date to compare today's sales against, no data is missing as there is an instance for every combination of variables on each of the 60 day's date added data.

View solution in original post

2 REPLIES 2
S_JB
Resolver III
Resolver III

To resolve this issue I have altered the cross join. I have taken distinct date added and a separate list which is distinct customer, sales person and item no. The cross join is then performed on these two lists with a limitation to only include instances which do not already exist in the dataset.

 

This means if I select a date to compare today's sales against, no data is missing as there is an instance for every combination of variables on each of the 60 day's date added data.

amitchandak
Super User
Super User

@S_JB , you can try measure like these with date table

 

Rolling 60 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-60,Day))
Rolling 60 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max('Date'[Date]),-60,Day))

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

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

August 2025 community update carousel

Fabric Community Update - August 2025

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