topic Re: TOTALYTD or CALCULATE in DAX Commands and Tips

TOTALYTD or CALCULATE

Tidous972 — Tue, 28 Sep 2021 19:13:53 GMT

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

Re: TOTALYTD or CALCULATE

colacan — Tue, 28 Sep 2021 22:10:24 GMT

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.

Re: TOTALYTD or CALCULATE

Tidous972 — Tue, 28 Sep 2021 22:53:03 GMT

Hi Colacan,

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

Re: TOTALYTD or CALCULATE

Tidous972 — Tue, 28 Sep 2021 23:01:08 GMT

Re: TOTALYTD or CALCULATE

VahidDM — Wed, 29 Sep 2021 01:07:08 GMT

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

Re: TOTALYTD or CALCULATE

Tidous972 — Wed, 29 Sep 2021 17:39:04 GMT

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.

Re: TOTALYTD or CALCULATE

VahidDM — Wed, 29 Sep 2021 23:18:45 GMT

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

Re: TOTALYTD or CALCULATE

Tidous972 — Wed, 29 Sep 2021 23:51:46 GMT

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.

Re: TOTALYTD or CALCULATE

VahidDM — Thu, 30 Sep 2021 01:13:10 GMT

Tidous972

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

Re: TOTALYTD or CALCULATE

Tidous972 — Thu, 30 Sep 2021 15:56:05 GMT

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.

Re: TOTALYTD or CALCULATE

Tidous972 — Thu, 30 Sep 2021 18:02:18 GMT

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.