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 have been searching for this solution in the community and have seen similar posts but nothing works for me yet.
I want to create a KPI card comparing YTD (year-to-date) sales and LYTD (last-year-to-date) sales.
I have a table with sales and dates.
I wrote two measures for YTD sales and LYTD sales.
YTD Sales = TOTALYTD(SUM(Table[Sales Dollars]), Table[Dates]) or CALCULATE(SUM(Table[Sales Dollars]), DATESYTD(Table[Dates]))
LYTD Sales = TOTALYTD(SUM(Table[Sales DOllars]), SAMEPERIODLASTYEAR(Table[Dates])) or CALCULATE(SUM(Table[Sales Dollars]), DATESYTD(DATEADD(Table[Dates], -1, YEAR)))
I put YTD Sales in Indicator, LYTD in Target Goal, and Year from a Date table in Axis.
YTD appears correctly, but LYTD shows (Blank) causing the difference to be infinity.
The measures work correctly in a Card but somehow the KPI Card doesn't show it correctly.
Any suggestion what is wrong and what I didn't consider?
Thank you.
Solved! Go to Solution.
Hi @Anonymous ,
Like this? If not please show me the reproducible sample data and the expected output value.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , You should use date table for that
example
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
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 :radacad sqlbi My Video Series Appreciate your Kudos.
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.
I did what you suggest in changing the date in the function from the date column in sales table to the date column in date table.
The target goal is no longer blank but showing last year sales.
However, it's not showing YTD last year sales, but total sales of last year.
Any suggestion or sample pbi file you could share?
Thank you in advance!
Hi,
Thank you for providing your solution.
But looking at your data, LYTD is still wrong.
In your KPI, it shows as 397, which is actually the total of the whole sales table.
And that is actually sales of this year plus last year.
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 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |