Forum Discussion
QOQ Distinct Accounts calculation
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.
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.
- Ashish_Mathur5 years ago
Super User
Hi,
Share the link from where i can download your PBI file.
- BItoken5 years ago
Helper III
https://1drv.ms/u/s!Astvd3j6-QR0c5SaviFjhg1vhew?e=2gCbxJ
Ashish_Mathur here is a sample of the pbix file
- Ashish_Mathur5 years ago
Super User
- amitchandak5 years ago
Super User
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- BItoken5 years ago
Helper III
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)