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.
I created a Line and Clustered Column Chart to display sales from 2011 to 2016. I want to display ALL sales of current year and sales of previous year from 2011 to 2015. However, I don't have intact sales of 2016. Therefore, I want to compare sales YTD of 2016 and sales YTD of 2015 comparing 2016 in the same chart. It displays sale of 2015 and sales YTD of 2016 now. I contacted technical support. He told me that I can use forecasting. I don't think forecasting would help. Anyone can help?
Solved! Go to Solution.
Hi @yaolin512,
For your requirement, you’d better use SAMEPERIODLASTYEAR and TOTALYTD function. SAMEPERIODLASTYEAR Function Returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, it will change with actual date is you use it combine TOTALYTD function. I try to reproduce and get the expected result as follows.
My sample sale data is from 2013/1/1 to 2015/7/20. I create measure use the following formulas.
Total Sales = SUM(Sales[SALE]) Total Sales YTD = TOTALYTD(Sales[Total Sales],'Sales'[DATE]) Total Sales YTD Last Year = CALCULATE([Total Sales YTD],SAMEPERIODLASTYEAR('date'[date]))
Then create a table used to display the results. Please not the highlighted in yellow background, Total Sales YTD will calculated the sum of sales during 2015/1/1 to 2015/7/20 in 2015 year. While the sales are full in 2013 and 2014, the Total Sales YTD Last Year measure calculates the sum of sales in whole last year.
Finally, create a Line and Clustered Column Chart, it return the expected result shown in the following screenshot.
If you have any other issue, please feel free to ask.
Best Regards,
Angelia
Thanks for your time and help Ashish.
The figure which you have shown in the pics are for the month of Jan which is correct figure but only for Jan and with selection....
See the snapshot below, highlighted
I get the highlighted figures after selecting months from Jan to May... and i need the same result without selecting the Months from Jan to May.... and when the current month is June then the result shoud show from Jan to Jun without any selection...
Regards,
Hi,
So you just want to select Year 2019 and June and the 2 measures should add up from:
Is my understanding correct?
Yes, if current month is Jun...need YTD/LYTD from Jan till date (Current Month)
Hi,
My solution is working fine. If you select Year 2019 and Month as only 5, the result is correct. Check again.
Result is correct but it should come without selection, Anyway thanks for your time Ashish...
Hi,
How should the "Month till" be determined? Should it be till the month of Today?
Yes...i think now it is clear for you
Hi,
Try these measures:
Measure = CALCULATE([Total Bal],DATESBETWEEN(DimTable[Date],DATE(YEAR(TODAY()),1,1),today()))
Measure 2 = CALCULATE([Total Bal],DATESBETWEEN(DimTable[Date],DATE(YEAR(TODAY())-1,1,1),EDATE(today(),-12)))
Hope this helps.
This solution is perfect for what I'm currently working on! I just need one slight tweak. On your second measure you have listed there, is it possible to bring in Prior YTD Sales, minus one day? ie We report 6/12/19 data on 6/13/19, and when comparing YTD to YTD, the measure given has results that compare YTD 6/13/18 to YTD 6/12/19. I just need to move the parameters for the LYTD measure back one day. I've experimented in the syntax of the measure and can't get that date moved back.
Hi,
Try this measure
Measure 2 = CALCULATE([Total Bal],DATESBETWEEN(DimTable[Date],DATE(YEAR(TODAY())-1,1,1),EDATE(today(),-12)-1))
Hope this helps.
WOW, Finally this works... Thanks Ashish...
Measure 2 = CALCULATE([Total Bal],DATESBETWEEN(DimTable[Date],DATE(YEAR(TODAY())-1,1,1),EDATE(today(),-12)))
Once again thanks a lot for your effort and time... ^_^
You are welcome. If my reply helped, please mark it as Answer.
Thanks Ashish,
I did missed that, now when i make the relation between the two table, i get correct figure but there is one thing that last year figure in the table i don't get. it shows only current year figure... and i believe this is due to the selection we made...
https://drive.google.com/file/d/1T_ZbDf-w8DMkyPMhJuu7IOYmXNMnTPBZ/view?usp=sharing
Can you display the difference of this year ytd and last year ytd .
Difference of 2 YTDS = YTD of this year - YTD of Last Year.
i am getting error.
please solve this
Thanks
sandeep
Thank you Angelia, that worked perfectly for me!
Angelina,
In your example, I want to have
year 2013: total sales of 2013
year 2014: total sales of 2013 and total sales of 2014
year 2015: sales from 1/1-7/20 in 2014 and sales from 1/1-7/20 in 2015
I don't have any problems using my formula to display YTD sales each year. It is just the YTD sales of last year I am struggling. Thanks!
Hi @yaolin512,
OK, I test the function step by step. For the solution I posted, Date will change with actual date is you use SAMEPERIODLASTYEAR function combining TOTALYTD function in same CALCULATE(). The measures given will calculate:
year 2013: total sales of 2013 is 17638.
year 2014: total sales of 2013 and total sales of 2014 17638 and 18649.
year 2015: sales from 1/1-7/20 in 2014 and sales from 1/1-7/20 in 2015 11088 and 9573.
Please see the following formula and screenshot.
total sale in 2013 = CALCULATE(SUM(Sales[SALE]),YEAR(Sales[DATE])=2013)
total sale in 2014 = CALCULATE(SUM(Sales[SALE]),YEAR(Sales[DATE])=2014)
total sale during 2015/1/1 to 2015/7/20 = CALCULATE(SUM(Sales[SALE]),AND(Sales[DATE]<=DATE(2015,7,20),Sales[DATE]>=DATE(2015,1,1)))
total sale during 2014/1/1 to 2014/7/31 = CALCULATE(SUM(Sales[SALE]),AND(Sales[DATE]<=DATE(2014,7,31),Sales[DATE]>=DATE(2014,1,1)))
If you calculate the sum from 2014/1/1 to 2014/7/20 using another measure, we we are not able to add multiple measure for same dimension. I will still test and update here.
Best Regards,
Angelia
Please review and test, you will find the function of SAMEPERIODLASTYEAR and TOTALYTD.
Best Regards,
Angelia
How about if I want to view the data of the current month till the starting of the year for all the years, and I have data at the ending of each week which I want to see.
For Example:
year 2018= want to see data of 1 Jan to 3 March
year 2017= want to see data of 1 Jan to 3 March (Contains data of all the years)
year 2016= want to see data of 1 Jan to 3 March (Contains data of all the years)
year 2015= want to see data of 1 Jan to 3 March (Contains data of all the years)
year 2014= want to see data of 1 Jan to 3 March (Contains data of all the years)
How to achive the above.
Angelia,
Somehow, I applied the wrong formula for sales YTD last year yesterday. I tried your formulas again today and worked! Many thanks!!!
Thank you everyone else!!! I appreciated it!
Look this blog , i guess it will give u some idea.
https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/
if not let me know i will help u.
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |