Get Last Year Month using DateAdd DAX from Calculated New Column

---

krishnaoptif wrote:

...now i need to create the matrix where SiteName willl be in rows, and need to add few % change Columns like (% change for sum of CountValue from Current Month[Oct-2016] Vs Previous Month[Sep-2016], Current Month[Oct-2016] Vs Last Year the Same Month[Oct-2015], Current Month - previous two months [Aug-2016] Vs Last Year Same Same Month [Aug-2015]

 

 

---

The way you are thinking about this problem is appopriate for Excel, but it is the wrong approach for Power Pivot.  This is what you need to do.

1.  Create a calendar table.  This table should contain a month column (which you actually have as a data column using the first day of the month - this is fine) and an ID column.  Read my article I posted above.  Let's assume your calendar table is called calendar and the columns are called Month, ID.

2.  Connect your data table to your calendar table on the month column

3.  The measures you need to do what you want will be as follows  (Just follow the pattern for other measures)

Chg vs Prior Month = calculate(sum(db[countvalue]),filter(all(calendar),calendar[ID] = max(calendar[ID])-1))

Chg vs Same Month PY= calculate(sum(db[countvalue]),filter(all(calendar),calendar[ID] = max(calendar[ID])-12))

Rolling 3 Months this year = calculate(sum(db[countvalue]),filter(all(calendar),calendar[ID] >= max(calendar[ID])-2 && calendar[ID] <=max(calendar[ID]))

Rolling 3 Months last year = calculate(sum(db[countvalue]),filter(all(calendar),calendar[ID] >= max(calendar[ID])-14 && calendar[ID] <=max(calendar[ID])-12)