Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Solved! Go to Solution.
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:
Calendar = CALENDARAUTO()
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))
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.
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:
Calendar = CALENDARAUTO()
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))
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.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCheck out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
104 | |
99 | |
97 | |
41 | |
38 |
User | Count |
---|---|
151 | |
123 | |
79 | |
73 | |
71 |