QOQ Distinct Accounts calculation

[BItoken]

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

[amitchandak]

@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]

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.

[amitchandak]

@BItoken , if you have a date you can use a date table and use DatesQTD formula.

Else you have to opt for the Rank way

Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839

[BItoken]

@amitchandak

@amitchandak 

  the rank func does not work.

Is it because im deiving the Q from the end date which is already rolled up to show just one date (End of Qtr)

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[BItoken]

https://1drv.ms/u/s!Astvd3j6-QR0c5SaviFjhg1vhew?e=2gCbxJ 

@Ashish_Mathur  here is a sample of the pbix file

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.