Forum Discussion

LandonDodge's avatar
LandonDodge
Microsoft Employee
8 years ago
Solved

Units for prior dates

My simple table has a Date column with date entries for each month from July 2016 through June 2017 (so twelve months total) and a Units column with the number of units corresponding to each month.  ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    Create a Calendar Table with a relationship from the Date column of the Sales table to the Date column of the Calendar Table.  Try these measures:

     

    Units sold in final month = CALCULATE(SUM('Sales'[Units]),DATESBETWEEN(Calendar[Date],EOMONTH(MAX(Calendar[Date]),-1)+1,EOMONTH(MAX(Calendar[Date]),0))))

     

    Units sold in month before final month = CALCULATE(SUM('Sales'[Units]),DATESBETWEEN(Calendar[Date],EOMONTH(MAX(Calendar[Date]),-2)+1,EOMONTH(MAX(Calendar[Date]),-1))))

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

Create a Calendar Table with a relationship from the Date column of the Sales table to the Date column of the Calendar Table.  Try these measures:

 

Units sold in final month = CALCULATE(SUM('Sales'[Units]),DATESBETWEEN(Calendar[Date],EOMONTH(MAX(Calendar[Date]),-1)+1,EOMONTH(MAX(Calendar[Date]),0))))

 

Units sold in month before final month = CALCULATE(SUM('Sales'[Units]),DATESBETWEEN(Calendar[Date],EOMONTH(MAX(Calendar[Date]),-2)+1,EOMONTH(MAX(Calendar[Date]),-1))))

LandonDodge's avatar
LandonDodge
Microsoft Employee
8 years ago

Thank you, Ashish_Mathur!  That is exactly what I was looking for and I very much appreciate your help!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    You are welcome.