Comparison based on Date Added

[S_JB]

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! 

[S_JB · Accepted solution]

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]

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