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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Shackwell
Helper III
Helper III

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

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
Super User
Super User

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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