topic Re: QOQ Distinct Accounts calculation in Desktop

QOQ Distinct Accounts calculation

BItoken — Thu, 08 Oct 2020 00:10:32 GMT

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

Re: QOQ Distinct Accounts calculation

amitchandak — Thu, 08 Oct 2020 00:56:21 GMT

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.

Re: QOQ Distinct Accounts calculation

BItoken — Thu, 08 Oct 2020 02:11:06 GMT

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.

Re: QOQ Distinct Accounts calculation

Ashish_Mathur — Thu, 08 Oct 2020 02:51:46 GMT

Hi,

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

Re: QOQ Distinct Accounts calculation

BItoken — Thu, 08 Oct 2020 03:18:51 GMT

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

Ashish_Mathur here is a sample of the pbix file

Re: QOQ Distinct Accounts calculation

amitchandak — Thu, 08 Oct 2020 03:19:33 GMT

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

Re: QOQ Distinct Accounts calculation

BItoken — Thu, 08 Oct 2020 22:10:24 GMT

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)

Re: QOQ Distinct Accounts calculation

Ashish_Mathur — Fri, 09 Oct 2020 01:43:23 GMT

Hi,

You may download my PBI file from here.

Hope this helps.