cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Shackwell
Helper I
Helper I

Same Period Last year vs Current

Greetings,

 

I have the following columns on 2 data bases. One from 2021 and one from 2020. So I wanted to check how can I make a measure to compare vs prior last year, on a monthly basis. (In this case for July 2020 and 2021)

Relevant Columns,

 

Date, Department, Division,  Cost, 

 

Thank you,

 

 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @Shackwell ,

I created some data:

2020.7.1 – 2021.12.3 dates

vyangliumsft_0-1629179970107.png

Encountered the problem you encountered, need a calendar table.

Here are the steps you can follow:

1. Create calendar table.

Date = CALENDAR(DATE(2020,7,1),DATE(2021,12,5))

2. Create measure.

Current date = SUMX(FILTER('Table',MONTH('Table'[Date])=MONTH(MAX('Date'[Date]))&&YEAR('Table'[Date])=YEAR(MAX('Date'[Date]))),[Cost])
Period Last year month = SUMX(FILTER('Table',MONTH('Table'[Date])=MONTH(MAX('Date'[Date]))&&YEAR('Table'[Date])=YEAR(MAX('Date'[Date]))-1),[Cost])
Result =
IF(
    [Period Last year month]=BLANK(),BLANK(),[Period Last year month]-[Current date])

3. Result:

vyangliumsft_1-1629179970111.png

 

There are similar solutions, you can check the following link:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Time-Intelligence-Quick-Measures-Problem/m-p/...

https://stackoverflow.com/questions/51217416/quick-measure-error-only-power-bi-provided-date-hierarc...

https://community.powerbi.com/t5/Service/Time-intelligence-does-not-work/td-p/279520

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

5 REPLIES 5
v-yangliu-msft
Community Support
Community Support

Hi  @Shackwell ,

I created some data:

2020.7.1 – 2021.12.3 dates

vyangliumsft_0-1629179970107.png

Encountered the problem you encountered, need a calendar table.

Here are the steps you can follow:

1. Create calendar table.

Date = CALENDAR(DATE(2020,7,1),DATE(2021,12,5))

2. Create measure.

Current date = SUMX(FILTER('Table',MONTH('Table'[Date])=MONTH(MAX('Date'[Date]))&&YEAR('Table'[Date])=YEAR(MAX('Date'[Date]))),[Cost])
Period Last year month = SUMX(FILTER('Table',MONTH('Table'[Date])=MONTH(MAX('Date'[Date]))&&YEAR('Table'[Date])=YEAR(MAX('Date'[Date]))-1),[Cost])
Result =
IF(
    [Period Last year month]=BLANK(),BLANK(),[Period Last year month]-[Current date])

3. Result:

vyangliumsft_1-1629179970111.png

 

There are similar solutions, you can check the following link:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Time-Intelligence-Quick-Measures-Problem/m-p/...

https://stackoverflow.com/questions/51217416/quick-measure-error-only-power-bi-provided-date-hierarc...

https://community.powerbi.com/t5/Service/Time-intelligence-does-not-work/td-p/279520

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

Hello.

 

I am rather new in this time analysis experiment. I did not have any problema creating the calendar. But can you further explain the 2 measures you put in here? I am a bit confused of which items had to be written within the formula.

goncalogeraldes
Solution Supplier
Solution Supplier

If I understood your question correctly what you want is an YoY variance. To accomplish so you can either use a quick measure or create a custom one like so: 

Amount YoY% = 
var __PREV_YEAR =
	CALCULATE(
		SUM(Column_to_Sum),
		DATEADD(Date_column, -1, YEAR)
	)
var __THIS_YEAR = SUM(Column_to_Sum)

return
	DIVIDE( __THIS_YEAR - __PREV_YEAR, __PREV_YEAR)
)

 

Please change the formula accordingly!

 

Hope this answer solves your problem! If you need any additional help please tag me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!

Best regards,
Gonçalo Geraldes

Hello I get the following.

 

  4 | ERROR("Time intelligence quick measures can only be grouped or filtered by the power bi provided date hierarchy or primary date column.

 

 

Which is strange because I have one column as "date" that shows date hierarchy in the model. But I do have another column that says in text message the "Month", not sure if that would case any interference.

 

 Also I would not be inclined to do a day(period 1) vs day(period 0) comparison. I would be more interested on comparing those on a monthly basis.

 

Thanks in advance

I have two questions for you:

  1. Do you a date dimension in your model?
  2. Are you using that column with the hierarchy in the measure?

I dont think the "Month" column interferes in this case but I would need a sample .pbix file to better understand the situation. Regarding your preference on the monthly analysis, when creating visual based on date hierarchies you can chooses the part it to use, in this case the month alone.

 

Hope this answer helps! If you need any additional help please tag me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!

Best regards,
Gonçalo Geraldes

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.