Calculate Previous Fiscal QTD

[Anonymous]

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 

[amitchandak]

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

[Anonymous]

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

[Anonymous]

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

[amitchandak]

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

[Anonymous]

@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 

[amitchandak]

@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

[Anonymous]

@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 

[amitchandak]

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

[MFelix]

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.

[Anonymous]

@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 

[MFelix · Accepted 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.

[Anonymous]

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

[Anonymous]

Hi @MFelix,

Actually I figured this out, the only thing I need to do is to change the lookupvalue function to:

DATESBETWEEN(Dates[Date],MAX(Dates[Date]),MAX(Dates[Date])

Then this will return the maximum date of my date slicer. 

Again appreciate both your help @MFelix @amitchandak 

Cheers,

Dennis 

[MFelix]

Hi @Anonymous ,

As I refered the measure could use some small changes glad you were abble to figure it out.