Forum Discussion

BItoken's avatar
BItoken
Icon for Helper III rankHelper III
5 years ago

QOQ Distinct Accounts calculation

Hi All, We have started collecting data since the last 2 quarters on accounts and we have a rolled up date field which is date for the Quarter end and im trying to get the QOQ for the current quarter see in pic below. The QOQ will have to be updated after each qtr going forward and the data is being captured. 

I have tried various DAX functions to calculate this but do not get a result.

 

measure = CALCULATE(DISTINCTCOUNT(account_id), end_period = MAX(end_period)-1)
Get an error: A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

 

 

Any help and suggestions is much appreciated!!

General Comment
Need Help
Tips and Tricks

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    BItoken , In case you Qtr Start from Jan, Apr,Jul or Oct. You can use time intelligence. DatesQTD.

     

    QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
    Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

    Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
    Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
    Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
    Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,Year))))
    trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))

    In case Qtr does start with those for month. You need use qtr start date / YYYYQQ format to create  Rank and use that. This can done in date table or QTR table. But should be separate table


    Qtr Start Date = DATEADD(STARTOFYEAR('Date'[Date],"4/30"),QUOTIENT(DATEDIFF('Date'[Start Of Year], 'Date'[Date],MONTH),3)*3,MONTH)
    Qtr Month No = DATEDIFF('Date'[Qtr Start Date],'Date'[Date],MONTH)+1
    Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)
    This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
    Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

    Appreciate your Kudos.

    • BItoken's avatar
      BItoken
      Icon for Helper III rankHelper III
      5 years ago

      Hi @amitchandak

      Thanks for your response.

      I already have the Quarter infor derived from the end of period date which is "FQ" so not sure how the calcs you mentioned will help here. Also, our FY Q1 starts in April to June and FY Q4 ends in Jan to March.