Forum Discussion

rikisax's avatar
rikisax
New Member
5 years ago
Solved

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!

Need Help
Tutorial Requests
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    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.

    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.

2 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    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.

  • v-robertq-msft's avatar
    v-robertq-msft
    Community Support
    5 years ago

    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.

    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.