Forum Discussion

S_JB's avatar
S_JB
Resolver III
5 years ago
Solved

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, sal...
Need Help
  • S_JB's avatar
    S_JB
    5 years ago

    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 avatar
amitchandak
Super User
5 years ago

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