Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
krishnaoptif
New Member

Get Last Year Month using DateAdd DAX from Calculated New Column

Hi Experts,

 

Firstly i have created one new calculated DAX column to know last date from the table

Site_LastMonthDate = Max(DB[MonthName])

 

 

Now going to take Same month using below calculated column  (DAX) for previous year based on Site_LastMonthDate (which is already calculated field to get last month inthe table)

 

Site_LasYearLastMonthName = DATEADD(DB[Site_LastMonthDate].[Date],-12,MONTH)

 

This above function is not working for the last 12 months name but if i run this for the same year like previous month like Site_LasYearLastMonthName = DATEADD(DB[Site_LastMonthDate].[Date],-1,MONTH) then this works.

 

It is very surprising. Can someone help me in this on urgent basis if possible.

 

1 ACCEPTED SOLUTION


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

 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

9 REPLIES 9

You should take a different approach. Read my article about calendar tables here 

http://exceleratorbi.com.au/power-pivot-calendar-tables/

 

Once you have a calendar table, you can write a measure (not a calc column) something like this

 

sales same month last year = calculate(sum(db[value]),dateadd(calendar[date],-1,year))



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Thanks for reply but firstly I need to get the max date from the existing date field then I need to get last year same month from the max date. May u please help me in this ?

You have a view of what you need to do, but I'm not clear if your approach is correct or not. Can you describe the output you want.  Just use Excel to show what your looking for and post an image



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Hi Matt, Below is the data screen shot where we have data till Oct-2016 (which is the current month based on the data) 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]

 

 

That is why i was adding one new calculated column to get the Max date from the MonthName column and then was trying to calculate based on that : I was filtering value like below my formula which is working fine for current year calculation till Jan-2016 but when i apply minus -12 month then it gives null as it is moving to 2015 year:

 

Site_This Month-1 = SUMX(FILTER(DB,DB[MonthName]=DateAdd(DB[Site_LastMonthDate].[Date],-1,MONTH)),DB[CountValue])

 

When i Change -1 to -12 (as this will move to Oct-2015) then it is showing null value. 

Site_This Month-1 = SUMX(FILTER(DB,DB[MonthName]=DateAdd(DB[Site_LastMonthDate].[Date],-12,MONTH)),DB[CountValue])

 

DataImage.PNG

 

 

 


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

 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Hi Matt,

 

Thanks for providing more context, this is what I was trying to get at with my suggestion, I typically use date math with integer representation of the date as they also provide ordering columns for the date labels.

 

Thanks 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


i think your current month column should like yyyymm, then to get last year at the same time you would subtract 100.



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


No this can not work rich.

Hi Matt, Do you any good idea.

can you explain, why it will not work, I use this pattern all the time for data calculations. depending on how you have filtered you report/visual, you may have to clear filter context to allow the measure to find the prior periods.



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.