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

KPI with YTD and LYTD

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.

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @jyeh_behr ,

 

Like this? If not please show me the reproducible sample data and the expected output value.

v-lionel-msft_0-1603871124100.png

 

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.

View solution in original post

5 REPLIES 5
Super User IV
Super User IV

@jyeh_behr , 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.

 

 



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!

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 @jyeh_behr ,

 

v-lionel-msft_0-1602829706191.png

v-lionel-msft_1-1602829731752.png

 

Please refer to my .pbix file.

 

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.

 

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. 

Hi @jyeh_behr ,

 

Like this? If not please show me the reproducible sample data and the expected output value.

v-lionel-msft_0-1603871124100.png

 

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.

View solution in original post

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors