Units for prior dates

[LandonDodge]

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.  I want to end up with three measures that can be used in three different card visuals in Power BI:  Units for Final Month, Units for Month Before Final Month, and Units for Month Six Months Ago.  I have the calculation for the first one, Units for Final Month, but cannot figure out the other two.  (I would have thought I could assign a negative number counting backward from the Final Month at 0 and then filtered on the negative number associated with the desired month, but if that's possible I can't uncover it.)  Here is what I have that is giving me Units for Final Month, which works great:

MonthFinal = CALCULATE(SUM('Sales'[Units]),
  FILTER(ALL('Sales'[Date]) ,
 'Sales'[Date] = MAX('Sales'[Date])))

What would the DAX statements be to get the Units for Month Before the Final Month, and Units for Month Occurring Six Months ago?  I imagine it's straightforwarded but is eluding me.  Thank you for any help you can provide!

[Ashish_Mathur · Accepted solution]

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]

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

[Ashish_Mathur]

You are welcome.