cancel
Showing results 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

New Member

## year over year problem

Hi,
I have to display the comparison data between totals in current period and the same period in the previous year in a simple matrix or table but it must not show the date column and instead it must group by another column ("specie") .
What determines the period is given by the filter control.

Here is the data structure:

example of the main table data:

And the BI desktop:

The Filter control is on the RollCalendar (tha is flag as "date table") and the table above display right data (yellow). But I want the matrix on the right, grouped by "Specie" Column and in this case data are wrong.

The "Tot. plants previous year" measure is :

CALCULATE(SUM(BI_SpecieSettimaneOV[TotPlants]),SAMEPERIODLASTYEAR(Calendar[Date]))

but obviously not the correct formula.

can someone help?

Thanks!

1 ACCEPTED SOLUTION
Community Support

Hi, @rikisax

According to your description and sample picture, you want to get the Plants previous year based on the time period selected in the Slicer. I created some data based on your data and achieve this, you can take a look and find if it’s useful:

This is my test data:

1. Create a Calendar table and do not give them the relationship, because the value of [Plants previous year] will be filtered if the relationship is given:
``Calendar = CALENDARAUTO()``

1. Create two measures:
``````Plants perivous year =

var _mindate=MIN('Calendar'[Date])

var _maxdate=MAX('Calendar'[Date])

return

CALCULATE(SUM(BI_SpecieSettimaneOV[Plants]),

FILTER('BI_SpecieSettimaneOV',

[Date]>=DATE(YEAR(_mindate)-1,MONTH(_mindate),DAY(_mindate))&&

[Date]<=DATE(YEAR(_maxdate)-1,MONTH(_maxdate),DAY(_maxdate))))``````
``````Plants this year =

var _mindate=MIN('Calendar'[Date])

var _maxdate=MAX('Calendar'[Date])

return

CALCULATE(SUM(BI_SpecieSettimaneOV[Plants]),

FILTER('BI_SpecieSettimaneOV',

[Date]>=_mindate&&[Date]<=_maxdate))``````
1. Create a Slicer and place ‘Calendar’[Date], then a Matrix and place it like this:

And I guess this can roughly meet the requirement that you want.

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

Best Regards,

Community Support Team _Robert Qin

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

2 REPLIES 2
Community Support

Hi, @rikisax

According to your description and sample picture, you want to get the Plants previous year based on the time period selected in the Slicer. I created some data based on your data and achieve this, you can take a look and find if it’s useful:

This is my test data:

1. Create a Calendar table and do not give them the relationship, because the value of [Plants previous year] will be filtered if the relationship is given:
``Calendar = CALENDARAUTO()``

1. Create two measures:
``````Plants perivous year =

var _mindate=MIN('Calendar'[Date])

var _maxdate=MAX('Calendar'[Date])

return

CALCULATE(SUM(BI_SpecieSettimaneOV[Plants]),

FILTER('BI_SpecieSettimaneOV',

[Date]>=DATE(YEAR(_mindate)-1,MONTH(_mindate),DAY(_mindate))&&

[Date]<=DATE(YEAR(_maxdate)-1,MONTH(_maxdate),DAY(_maxdate))))``````
``````Plants this year =

var _mindate=MIN('Calendar'[Date])

var _maxdate=MAX('Calendar'[Date])

return

CALCULATE(SUM(BI_SpecieSettimaneOV[Plants]),

FILTER('BI_SpecieSettimaneOV',

[Date]>=_mindate&&[Date]<=_maxdate))``````
1. Create a Slicer and place ‘Calendar’[Date], then a Matrix and place it like this:

And I guess this can roughly meet the requirement that you want.

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

Best Regards,

Community Support Team _Robert Qin

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

Super User

Hi @rikisax ,

Believe this is related with the fact that when you use a column on a calculate if the specific column is not used on your visualization it's applied and ALL filter making the value incorrect.

Can you share a sample file please see this post regarding How to Get Your Question Answered Quickly (courtesy of @Greg_Deckler) and How to provide sample data in the Power BI Forum (courtesy of @ImkeF).

If the information is sensitive please share it trough private message.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors