Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
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:
Solved! Go to 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.
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.
@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))