Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsIf 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:
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)
Thanks @amitchandak. I think we are almost there, however, if I am trying to return the max date selected, how should I modify the current var piece in your solution?
I tried the below but doesnt seem to work:
var _max = MAX(ALLSELECTED(Dates[Days in the Quarter]))
Thanks,
Dennis
Try
var _max = MAXX(ALLSELECTED(Dates),Dates[Days in the Quarter])
What is the error you get? Or simply check what does this gives. Is it give correct Qtr Days
Also vote for :https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/39772834-custom-period
@amitchandak Now I am getting the correct day in quarter returns. One last question and I think we can close this ask!
Let's say the last time I refreshed the data was 3/20/2020 and that was 50 days into the current fiscal quarter (Feb - Apr). Would it be possbile to get the first 50 days from the previous quarter without using the slicer (I believe the solution above is based off the slicer selected)? Basically creating another var that based on the last data refresh date?
Again appreciate your help!
Thanks,
Dennis
@Anonymous
Something like this
meaure =
var _max = maxx(Table,Table[Date])
var _maxday= maxx(filter(Date, date[Date]=_max),Date[Date of Qtr])
var _LastQtrRank= maxx(filter(Date, date[Date]=_max),Date[Qtr Rank]) -2
You can use these var in calc
@amitchandak the var works in the calculation but I am getting the same issue as I described in my original ask. I think essentially your solution will return the same result as mine - previous QTD calculation returns null if no billings observed on those days.
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
I am referring to this link previous quarter to date for the solution but doesnt seem to work perfectly. @MFelix please let me know if you are also be able to help/comment.
Thanks,
Dennis
@Anonymous
Can you share a better sample with Date in mm/dd/YYYY format, I will try to build a pbix and share.
Copy-paste a table data.
Hi @Anonymous ,
The solution you refer to a simple calendar that picks up the quarte based on the normal dates, believe that your issues may refer to the way you are calculating non standard dates, and probably the start and end date of the quarter is not getting the correct value since the Sales[Date] column is connected to the normal date.
As @amitchandak if you can share sample file would be easier to give you an answer.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@amitchandak @MFelix Thank you both for taking the time and looking for solution!
I've attached a sample .pbix file here: qtd sample.pbix
Based on the latest date of sales data (3/22/2020), the previous QTD calculation should grab the first 51 days from the previous quarter and should return $286,000 as the result (sales from 12/6/2019 and 12/9/2019).
However, both of the calculated measures returned blank. PreviousQTD is the orginal solution and PreviousQTD_test is the solution from @amitchandak. Quick screenshot below:
Please let me know if I can provide anything else to get this resolved.
Thanks!
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix ,
Thanks for this alternate solution. I did give it a try but seems like it only works for the current quarter. For the previous quarter, the var NUMBER_OF_DAYS will return blank if I have a range of dates selected. See quick screenshot below:
How would I return the number of days betwen the maximum of the selected dates (from a range) and the quarter start date?
Sample .pbix file attached here: qtd sample_1.pbix
Thank you again for your help!
Best,
Dennis
Hi @MFelix,
Actually I figured this out, the only thing I need to do is to change the lookupvalue function to:
Hi @Anonymous ,
As I refered the measure could use some small changes glad you were abble to figure it out.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsJoin the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
58 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |