The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi guys,
I need help on the tooltip. I have a bar chart in a report page that displays the averages of the category based on its last 12 months. Now, I want to have a tooltip that compares current year vs previous year averages. However, when I do this, it only shows the last 12 months on the report page since the page is filtered to last 12 months.
Is it possible to show comparison between current and previous year on a report page which have page filters applied to the last 12 months?
Thanks.
Solved! Go to Solution.
@Anonymous , If you are using a tooltip page then you can can avoid passing the time by switch off keep all filters and drag the required filters below it.
Also with time intelligence you should be able to show last year same month or last period same month
using a measure like
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))
Hi, @Anonymous
try to create measures like below:
CurrentYear =
var _maxyear=MAXX(ALLSELECTED(Table1),'Table1'[Year])
var _r=
IF(HASONEVALUE(Table1[Year]),
CALCULATE(
SUM(Table1[Sale]),'Table1'[Year]=SELECTEDVALUE(Table1[Year])),
CALCULATE(
SUM(Table1[Sale]),'Table1'[Year]=_maxyear))
return _r
LastYear =
var _maxyear=MAXX(ALLSELECTED(Table1),'Table1'[Year])-1
var _r=
IF(HASONEVALUE(Table1[Year]),
CALCULATE(
SUM(Table1[Sale]),'Table1'[Year]=SELECTEDVALUE(Table1[Year])),
CALCULATE(
SUM(Table1[Sale]),'Table1'[Year]=_maxyear))
return _r
Then create a tooltip page.
Or instead of using the tooltip page, you can use the small multiples feature to display multiple years of data.
Drag and drop the year field into the small multiples bucket.
Select the year you want to display in the filter or slicer.
Result:
Please refer to the attachment below for details.
Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
try to create measures like below:
CurrentYear =
var _maxyear=MAXX(ALLSELECTED(Table1),'Table1'[Year])
var _r=
IF(HASONEVALUE(Table1[Year]),
CALCULATE(
SUM(Table1[Sale]),'Table1'[Year]=SELECTEDVALUE(Table1[Year])),
CALCULATE(
SUM(Table1[Sale]),'Table1'[Year]=_maxyear))
return _r
LastYear =
var _maxyear=MAXX(ALLSELECTED(Table1),'Table1'[Year])-1
var _r=
IF(HASONEVALUE(Table1[Year]),
CALCULATE(
SUM(Table1[Sale]),'Table1'[Year]=SELECTEDVALUE(Table1[Year])),
CALCULATE(
SUM(Table1[Sale]),'Table1'[Year]=_maxyear))
return _r
Then create a tooltip page.
Or instead of using the tooltip page, you can use the small multiples feature to display multiple years of data.
Drag and drop the year field into the small multiples bucket.
Select the year you want to display in the filter or slicer.
Result:
Please refer to the attachment below for details.
Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , If you are using a tooltip page then you can can avoid passing the time by switch off keep all filters and drag the required filters below it.
Also with time intelligence you should be able to show last year same month or last period same month
using a measure like
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))