cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hmichael8
Helper II
Helper II

How to calculate QTD and YoY with custom fiscal quarters?

Hi all,

 

My table visualisation is set up like this; 

VolumeTarget% to targetYoY
30035086%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

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

19 REPLIES 19
v-gizhi-msft
Community Support
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

 

Ashish_Mathur
Super User
Super User

Hi,

Share a simple dataset and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Hi,

Share the link from where i can download your PBI file.  Please share only relevant information in that file.  To avoid confusion, remove unwanted tables.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi, here is the sample file link
https://drive.google.com/file/d/1LWgPHlxPFz80KFKxCrceWidiFiW40DSY/view?usp=sharing

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 

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.

Untitled.png

 

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

This didn't work

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a 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]))) 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a 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]))) 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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?

Hi,

What is the mistake in my solution?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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

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
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
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 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))
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://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Greg_Deckler
Super User
Super User

Check out my Time Intelligence the Hard Way measure in the Quick Measures gallery.

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!