cancel
Showing results for
Did you mean:  Helper II

## How to calculate QTD and YoY with custom fiscal quarters?

Hi all,

My table visualisation is set up like this;

 Volume Target % to target YoY 300 350 86% 110%

Where all of the above are measures based on my sales data.

I use a slicer to show these values based on my fiscal quarter.

I have a custom date table set up with my fiscal year, from Apr-Mar and I'd like to know how to calculate two things.
For example, when I choose Q4 as we are currently in Q4, I want % to target to compare current volume and the target to date, rather than for the whole quarter. So if we have 300 volume and the target as of 6/3/20 is 320, % to target should show 94%.
So Im looking for a way to do Sameperiodlastyear but up until the last date of volume data if possible?

Also, im looking for a way to show YoY figures for a whole year up until the last volume data date. So again, I would like to compare volume from this fiscal year to date 1/4/19 - 6/3/20 to 1/4/18 - 6/3/19. Currently my YoY function is taking the current volume and comparing against full year last year.

Thanks.

1 ACCEPTED SOLUTION  Super User

Hi,

Hope this helps. Regards,
Ashish Mathur
http://www.ashishmathur.com
19 REPLIES 19  Community Support

Hi,

Could you please share some sample data as a screenshot here if convenient.

Remember to remove any sensitive data.

Thanks.

Best Regards,

Giotto Zhi  Super User

Hi,

Share a simple dataset and show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com  Helper II

Q4 consists of Jan, Feb, Mar

Volume = count of rows in table one
Target = sum of target value in table two (each row of this table is a different day with a target value)

I currently have volume for Jan and Feb and targets for the whole year (jan, feb, mar)
Volume:

Jan = 50
Feb = 50
Total = 100

Target

Jan = 60
Feb = 60
Mar = 60

Total = 180

Currently my Q4 % to target is calculating like 100/180 = 56% but I want it to compare to same period that volume exists i.e. 100/120 = 83% (Jan and Feb only) and then when march is populated with volume then add that too.

Also, im looking for a way to show YoY figures for a whole year up until the last volume data date. So again, I would like to compare volume from this fiscal year to date 1/4/19 - 6/3/20 to 1/4/18 - 6/3/19. Currently my YoY function is taking the current volume and comparing against full year last year. See example below
1/4/19 - 6/3/20 volume = 500
1/4/18 - 6/3/19 volume = 520
1/4/18 - 31/3/19 (FY19) volume = 550

currently my YoY is calculating as 500/550 but it should be 500/520  Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com  Helper II

Hi, here is the sample file link

My issue occurs with whatever is the current quarter, currently it is q4 in 2020. Currently % to target is comparing volume to date to the target for the whole period. Instead, I want % to target to be comparing volume to date divided by target to date (this being the last date of sales data). Also, I want YoY to compare the same period last year to date - for example If i was to look at YoY figure today I want to compare; 1/4/19 - 7/3/20 to 1/4/18 - 7/3/19.

thanks  Super User

Hi,

Try these measures

``Target QTD to date = CALCULATE([Target],DATESBETWEEN('Calendar 5'[Date],MIN('Calendar 5'[Date]),today()))``
``Target qtd to date (SPLY) = CALCULATE([Target QTD to date],SAMEPERIODLASTYEAR('Calendar 5'[Date]))``

Hope this helps. Regards,
Ashish Mathur
http://www.ashishmathur.com  Super User

Try like

QTD Target = CALCULATE(SUM(Target[Target Amount]),DATESQTD(('Date'[Date])))

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])), filter('Date',is(notblank([QTD Target ])))

Proud to be a Super User!  Helper II

This didn't work  Super User

I am assuming this only for  last qtr

then try this

QTD Target =
var _Max = maxx(sale,[sales(Date)])

CALCULATE(SUM(Target[Target Amount]),DATESQTD(('Date'[Date])),filter('Date','Date'[Date]<_Max))

Also last version was on sales

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
QTD Target = CALCULATE(SUM(Target[Target Amount]),DATESQTD(('Date'[Date])),filter('Date',is(notblank([QTD Sales ]))))

Or

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
QTD Target = CALCULATE(SUM(Target[Target Amount]),values(Date[Month-Year]),DATESQTD(('Date'[Date])),filter('Date',is(notblank([QTD Sales ]))))

Proud to be a Super User!  Super User

@hmichael8 , this one i test. Also find the file

``Target QTD = CALCULATE(sum(Traget[Value]),values('Date'[Date]),DATESQTD('Date'[Date]),FILTER('Date','Date'[Date] in DATESQTD(Sales[Month]))) ``

Proud to be a Super User!  Super User

this one i test. Also find the file

``Target QTD = CALCULATE(sum(Traget[Value]),values('Date'[Date]),DATESQTD('Date'[Date]),FILTER('Date','Date'[Date] in DATESQTD(Sales[Month]))) ``

Proud to be a Super User!  Helper II

None of the suggestions are working. All I would like to do is calculate sum of target value between two dates. The first date should be the start date of the selected quarter and the second date should be the maximum date where sales data exists for that quarter only. Is this possible?  Super User

Hi,

What is the mistake in my solution?

Regards,
Ashish Mathur
http://www.ashishmathur.com  Helper II

Hi @Ashish_Mathur
The values I expected are as follows; For 2020

Q1: 135
Q2: 137

Q3: 137

Q4: 96 (target til 4th March because thats last sales date)

Your measure Target QTD to date was returning the following values;
Q1: 512
Q2: 377
Q3: 240
Q4: 103 (target til 9th March, because it's todays date)

I would just like to see the target for the quarter and if the quarter is not complete, I'd like to see the target between the start date of the quarter and the last sales date that falls within that quarter.  Super User

Hi,

Hope this helps. Regards,
Ashish Mathur
http://www.ashishmathur.com  Helper II

Thanks this worked. Could you please provide a brief explanation of your two measures so that I can understand them for future use?  Super User

You are welcome.  If my reply helped, please mark it as Answer.  I would request you to read up on the DATESBETWEEN and ALL functions.  If you still face problems, please post back.

Regards,
Ashish Mathur
http://www.ashishmathur.com  Super User

With date dimension, you can use datesytd and totalytd both can take end date

``````YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"3/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"3/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"3/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"3/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"3/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

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  4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER))
``````

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://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Proud to be a Super User!  Super User
Check out my Time Intelligence the Hard Way measure in the Quick Measures gallery.

Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!    