Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Hi @Anonymous ,

 

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
amitchandak
Super User
Super User

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

 

 

Anonymous
Not applicable

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

 

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.

 

Anonymous
Not applicable

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

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.