Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hey guys,
I am trying to display brand awareness, of various brands current quarter TD vs last quarter in a clustered column chart.
I can't finda way, how to filter out current and previous quarters only. Any suggestions?
Solved! Go to Solution.
Hi, @AlmantasPBIguy ;
You could create a flag column in table.
flag =
var _to=TODAY()
return IF(YEAR([Date])=YEAR(_to)&&QUARTER([Date])=QUARTER(_to),1,
IF(QUARTER(_to)=1,IF(YEAR([Date])= YEAR(_to)-1&&QUARTER([Date])=4,1),IF(QUARTER([Date])=QUARTER(_to)-1,1)))
Then apply it in all page filter.
The final output is shown below:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @AlmantasPBIguy ;
You could create a flag column in table.
flag =
var _to=TODAY()
return IF(YEAR([Date])=YEAR(_to)&&QUARTER([Date])=QUARTER(_to),1,
IF(QUARTER(_to)=1,IF(YEAR([Date])= YEAR(_to)-1&&QUARTER([Date])=4,1),IF(QUARTER([Date])=QUARTER(_to)-1,1)))
Then apply it in all page filter.
The final output is shown below:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks, this helped me out!
@AlmantasPBIguy , Measure using date table and TI
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
based on today
QTD Today=
var _month = mod(month(Today()),3)
var _min = date(year(today()),month(Today()) -1* if(_month=0,3,_month) ,1)
var _day = today()
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _day) )
LQTD Today=
var _month = mod(month(Today()),3)
var _min = eomonth(date(year(today()),month(Today()) -1* if(_month=0,3,_month) ,1),-4)+1
var _day = date(year(today()), month(today()) -3 , day(today()))
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _day) )
Thank you, bur this does not work for me, because I need to compare current quarter and last quarter brand by brand. All I need is a way to filter this quarter and last quarter on the page level. Perhaps a true/false column that returs true if it is current or last qtr?