Forum Discussion
Calculate Previous Fiscal QTD
Hi,
I have a Previous QTD calculation to capture true QTD for the previous quarter. The fiscal quarter is non-standard so I have to create this calculation instead of using default DAX Time functions. Fiscal quarters are Feb-April, May-July, Aug-Oct, Nov-Jan. So for example, if the current QTD is 2/1 - 3/24. My previous QTD will return the sum of sales for 11/1 - 12/23 from the previous quarter (basically just grab the first N days from the previous quarter depends on the date in the current quarter)
Here's my formula. Date table is a customized fiscal date table.
PreviousQTD =
VAR Previous_Quarter = DATEADD(Dates[Start of Q],-1,QUARTER)
VAR Previous_Quarter_end =
DATEADD(Dates[End of LQ],0,QUARTER)
VAR Current_Quarter_Start = DATEADD(Dates[Start of Q],0,QUARTER)
VAR Max_Date = MAX (Sales[Date])
RETURN
CALCULATE (
[Billings $],
FILTER(ALL(Dates),
Dates[Date] >= Previous_Quarter
&& Dates[Date]
<= IF (
Previous_Quarter_end
<= Previous_Quarter + DATEDIFF ( Current_Quarter_Start, Max_Date, DAY ),
Previous_Quarter_end,
Previous_Quarter + DATEDIFF ( Current_Quarter_Start, Max_Date, DAY )
)
)
)
The expected output should look like:
Date PreviousQTD Billings$
2/25 $290,000
3/6 $220,000
3/9 $66,000
Total $286,000 $290,000
However, I got the output like the below:
Date PreviousQTD Billings$
2/25 $290,000
3/6
3/9
Total $290,000
For some reason the Previous QTD calculation will return blank if no billings are shown on that specfic day. I also tried using DAX calculation for the last quarter billings:
Last Quarter Billings $ = CALCULATE([Billings $],DATEADD(Dates[Date],-1,QUARTER))
This works but as I mentioned earlier, it will return a full previous quarter when I do the QoQ comparison. What I really want to do is to compare the first N day of the previous quarter. This formula will work like below:
Date Last Quarter Billings Billings$
2/25 $290,000
3/6 $220,000
3/9 $66,000
4/3 $180,000
5/5 $100,000
Total $466,000 $290,000
However, I do not want the full previous quarter billings, which means excluding 4/3/2020 and 5/5/2020, as we are currently not there yet. Can someone please help? Thanks in advance!
- Dennis
Hi Anonymous ,
since you have the columns start of quarter and end of quarter alongside with MAX date you just need to calculate the difference of the day make the following measure:
PreviousQTD = VAR NUMBER_OF_DAYS = IF(SELECTEDVALUE(Dates[FY&Q]) = LOOKUPVALUE(Dates[FY&Q];Dates[Date];SELECTEDVALUE(Dates[Max Date]));LOOKUPVALUE(Dates[Days in the Quarter];Dates[Date];SELECTEDVALUE(Dates[Max Date])) ;LOOKUPVALUE(Dates[Days in the Quarter];Dates[Date];SELECTEDVALUE(Dates[Date]))) RETURN CALCULATE ( SUM ( 'sample data'[Billings] ); FILTER ( ALL ( Dates ); Dates[Date] >= SELECTEDVALUE ( Dates[Start of LQ] ) && Dates[Date] <= SELECTEDVALUE ( Dates[Start of LQ] ) + NUMBER_OF_DAYS ) )Returns the result you need.
Be aware that since you don't have any next month data I'm not sure if this is calculating completly ocrrectly especcially when you have other selections. But the tough process is to find the number of days in the quarter and sum those days to LQ days. You can also make it based on the date selected for calculating number of days, so if you select the 23 of March wil return 51 but 25 february will return 25 days. Easy to adjust to this last option.
Check the PBIX file attach.
15 Replies
- amitchandak
Super User
If you Qtr are Jan-Mar, Apr-Jun, etc. Does not matter which one is 1st . the you can use the following with Date dimension
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 to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER))) Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER))) Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year))) trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER)) trailing 4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER))But if they no standard then create a rank on the qtr using YYYYQ, here Q is custom based on your month. And Create a rank
rank = Rank(All(Date),[YYYYQ],,asc,dense)
Now use this rank to calculate the previous qtr
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all(Date), Date[Qtr Rank]= Max(Date[Qtr Rank])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all(Date), Date[Qtr Rank]= Max(Date[Qtr Rank])-1))Refer this file of week for your reference
https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0
You have same for QTR
amitchandak thanks for the quick response.
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all(Date), Date[Qtr Rank]= Max(Date[Qtr Rank])-1))
This solution seems to capture the full previous quarter, instead of only capturing the first N days of previous quarter as I mentioned in the thread. Since I have a customized fiscal calendar date table, this function will do exactly the same:
Last Quarter Billings $ = CALCULATE([Billings $],DATEADD(Dates[Date],-1,QUARTER))Thanks,
Dennis- amitchandak
Create a Qtr Day by subtracting startdateofQtr and date.
Measure =
Var _max= (Allselected(Date),Date[Qtr Day])
Return
Last Quarter Billings $ = CALCULATE([Billings $],DATEADD(Dates[Date],-1,QUARTER), Date[Qtr Day]<_max)
