cancel
Showing results for
Did you mean:  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  Community Support

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/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

5 REPLIES 5  Community Support

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/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  Helper I

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.  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),
)
var __THIS_YEAR = SUM(Column_to_Sum)

return
DIVIDE( __THIS_YEAR - __PREV_YEAR, __PREV_YEAR)
)``````

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  Helper I

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.  Solution Supplier

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.

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   