cancel
Showing results for
Did you mean:
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
Community Support

Hi @jyeh_behr ,

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.

5 REPLIES 5
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

Tutorial Series Dax Vs SQL Direct Query PBI Tips

Proud to be a Super User!

Helper I

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?

Community Support

Hi @jyeh_behr ,

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.

Helper I

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.

Community Support

Hi @jyeh_behr ,

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.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform October Community Highlights

Check out the top community contributors across all of the communities

#### 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