TOTALYTD or CALCULATE

[Tidous972]

Dear All,

I have a Sales table ranging from 01/01/2019 to 08/31/2021 and I would like to compare the total sales but of course from January to August for each year. Can I use TOTALYTD.
Thanks for your help.
Regards

[VahidDM · Accepted solution]

Hi @Tidous972 

Try these measures:

For this year:

TotalYTD = TOTALYTD(sum('Table'[Value]),'Table'[Date])

For last Year:

TotalLYTD = TOTALYTD(sum('Table'[Value]),SAMEPERIODLASTYEAR('Table'[Date]))

For Year 2019 :

Total2LYTD = TOTALYTD(sum('Table'[Value]),DATEADD('Table'[Date], -2, year))

My data:

DateValue

31-Jan-19	100
28-Feb-19	200
31-Mar-19	300
30-Apr-19	400
31-May-19	500
30-Jun-19	600
31-Jul-19	700
31-Aug-19	800
30-Sep-19	900
31-Oct-19	1000
30-Nov-19	1100
31-Dec-19	1200
31-Jan-20	1300
29-Feb-20	1400
31-Mar-20	1500
30-Apr-20	1600
31-May-20	1700
30-Jun-20	1800
31-Jul-20	1900
31-Aug-20	2000
30-Sep-20	2100
31-Oct-20	2200
30-Nov-20	2300
31-Dec-20	2400
31-Jan-21	2500
28-Feb-21	2600
31-Mar-21	2700
30-Apr-21	2800
31-May-21	2900
30-Jun-21	3000
31-Jul-21	3100
31-Aug-21	3200

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

[Tidous972]

Hi VahidDM

It's Ok for the current year (01/31/21 to 08/31/21) but it's wrong for the previous years, the formulas don't work. For the last year and for 2019, i have the total of sales of the year and not the total of the same period of 2021 i.e January to August. (01/31/20 to 08/31/20 and 01/21/19 to 08/31/19). 

Thanks for your help.

[VahidDM]

Hi @Tidous972 '

Do you want to include value of January in the calculation? IF yes, based on my sample data those values from Measures are correct.

 Appreciate your Kudos!!

[Tidous972]

HI VadidDM,

Yes of course, I want to include the values of January. I have copied your formula exactly but it returns me the total sales for the year and not the partial sales from January to August for 2019 and 2020.Thank you for taking the time to help me.

[VahidDM]

@Tidous972 

Can you share a sample of your data in a table format (to be able to copy and past that)?

[Tidous972]

I VahidDM,

To follow up on my previous post, I found the origin of the error and is due to the relationship with the Date table of my model. When I delete the relation between Date table and the tables of Sales, the TotalYTD and Total2YTD formulas works, otherwise the visuals concerning the previous years are empty when the relationship is on. Do you have an idea.

[Tidous972]

Hi VahidDM,

I realized that you have to use the same table (TableSales'[Value]),SAMEPERIODLASTYEAR('TableSales'[Date])and not the calendar table, but this time the visual is empty. I don't understand why.

[colacan]

@Tidous972 Hi Tidous972,

It depends on your data because there are many ways of getting YTD sales based on data. please share sample data if possible.

[Tidous972]

Spoiler

Spoiler

Hi Colacan,

Thank you for your answer. I dont know how to share the .pbix file.

[Tidous972]