Forum Discussion

AlmantasPBIguy's avatar
AlmantasPBIguy
Frequent Visitor
4 years ago
Solved

Filtering Current + last quarter

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 q...
Need Help
Tutorial Requests
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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.

amitchandak's avatar
amitchandak
Super User
4 years ago

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

 

  • AlmantasPBIguy's avatar
    AlmantasPBIguy
    Frequent Visitor
    4 years ago

    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?