Skip to main content
cancel
Showing results for 
Search instead 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

Reply
rikisax
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:

img1.png

 

example of the main table data:

img2.png

 

And the BI desktop:

img3.png

 

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
v-robertq-msft
Community Support
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:

v-robertq-msft_0-1610617902691.png

 

  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()

v-robertq-msft_1-1610617902693.png

 

  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:

v-robertq-msft_2-1610617902730.png

 

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

You can download my test pbix file here

 

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

How to Get Your Question Answered Quickly 

 

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.

View solution in original post

2 REPLIES 2
v-robertq-msft
Community Support
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:

v-robertq-msft_0-1610617902691.png

 

  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()

v-robertq-msft_1-1610617902693.png

 

  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:

v-robertq-msft_2-1610617902730.png

 

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

You can download my test pbix file here

 

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

How to Get Your Question Answered Quickly 

 

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.

MFelix
Super User
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).

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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