Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
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
Hi,
Share a simple dataset and show the expected result.
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.
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.
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 ])))
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 ]))))
@Anonymous , 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])))
@Anonymous
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])))
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?
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.
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.
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/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |