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 quarters only. Any suggestions?

 

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.

4 Replies

  • 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? 

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    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.