Forum Discussion
Helper IIISame 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,
Hi Shackwell ,
I created some data:
2020.7.1 – 2021.12.3 dates
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:
There are similar solutions, you can check the following link:
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
5 Replies
- goncalogeraldes
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
ShackwellHello 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
- goncalogeraldes
I have two questions for you:
- Do you a date dimension in your model?
- 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
Hi Shackwell ,
I created some data:
2020.7.1 – 2021.12.3 dates
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:
There are similar solutions, you can check the following link:
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
- Shackwell
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.
