topic Re: Year to date, compare to last year, per category in DAX Commands and Tips

Year to date, compare to last year, per category

ImPalaBI — Tue, 29 Mar 2022 14:03:48 GMT

I need to compare cummulative or running total sales of this year, with cummulative or running total sales of last year (this part I think I have solved) but I need to show the categories of sales.

This is an example of my data:

Date	Category	Sales
01/01/2021	SalesCampaign1	100
01/02/2021	SalesCampaign2	300
01/01/2022	SalesCampaign3	50
01/02/2022	SalesCampaign3	100

I have this formula to calculate this year´s running total sales:

This year cummulative sales =

TOTALYTD(
SUM(venta_final[Sales]),
'Date'[Date])

And this one to calculate last year´s running total sales:

LY cummulative sales =

CALCULATE(venta_final[This year sales],

SAMEPERIODLASTYEAR('Date'[Date]))

This is what I get:

Date	This year cummulative sales	LY cummulative sales
01/02/22	400	150

But when I try to add the category to a visual like a table or a chart, it fails. (actually it works with this years sales, but it does not work combined with last year sales.

What I expect to get is a table/matrix/chart like this:

Date	Category	This year cummulative sales	LY cummulative sales
01/02/22	SalesCampaign3	150
01/02/22	SalesCampaign1	0	100
01/02/22	SalesCampaign2	0	300

(I will also like the line to stop once it reaches the last date with a sale)

Many thanks in advance to anyone willing to help.

Re: Year to date, compare to last year, per category

johnt75 — Tue, 29 Mar 2022 14:13:22 GMT

Try splitting the category out into a separate dimension table using

Sales Category = DISTINCT( 'Table'[Category])

then create a one-to-many relationship from that to your data table and use the new dimension table on your visuals.

Re: Year to date, compare to last year, per category

ImPalaBI — Tue, 29 Mar 2022 14:20:22 GMT

Thanks for the reply,

I actually have a separate table with the categories and tried to use it on the visuals, with no success, this is my model, I have many categories:

Re: Year to date, compare to last year, per category

Whitewater100 — Tue, 29 Mar 2022 14:28:03 GMT

Hi:

You are on the right track. Adding a your date table really helps. Please see attached file. I added a record to your sales table,which is not required but wanted to have a many to one relationship with your sales to date tables. I hope this solves the question!

https://drive.google.com/file/d/1gaMc-c5R7JqVIbNGyMGIPo5B51F4XMwz/view?usp=sharing

Re: Year to date, compare to last year, per category

ImPalaBI — Tue, 29 Mar 2022 20:11:05 GMT

I still can´t add any category as a legend in the file you sent.

Re: Year to date, compare to last year, per category

Whitewater100 — Tue, 29 Mar 2022 20:32:12 GMT

Hi:

Your category should generally be in your products dimension table. Can you add a column in Producto, using a function like LOOKUPVALUE or RELATEDTABLE to bring Category over.

Calc Col = LOOKUPVALUE(venta_final'[Category], venta_final[ID], **Producto[ID])

**If these are a match.

Then use Category from your product table and the measures shared earlier should work just fine..

Re: Year to date, compare to last year, per category

v-janeyg-msft — Fri, 01 Apr 2022 10:26:14 GMT

ImPalaBI

Have you tried Whitewater100 's advice? If your problem has been solved, you can mark the answer as solution to close the thread. If not, please tell me your current problem and feel free to ask me.

Best Regards,
Community Support Team _ Janey

Re: Year to date, compare to last year, per category

ImPalaBI — Sat, 02 Apr 2022 16:04:00 GMT

Yes, I have tried the advice, but is not what I´m looking for, I already answered him.
So far, no solution to my issue.

Re: Year to date, compare to last year, per category

Whitewater100 — Sat, 02 Apr 2022 20:40:17 GMT

Hi:

Can you please provide an example of your data model, so I can see how your are joining and what fields are being used, in which tables E.g, where are RB-522, S2iF3 XMA5521? Your inital example says sales category but your chart is based on these figures in some part.

Each item would need a sales cat code in your fact table and a unqiue sales category code would be in a separate table joined to the fact table.

More info will help answer your question in this case.

Thanks

Re: Year to date, compare to last year, per category

Whitewater100 — Sun, 03 Apr 2022 12:28:41 GMT

Hi:

I created an example model( dummy data ) that is a good way to obtain the results you want. Please see attached file. The products in the fact table join to the same and unique products in a dimension table, creating a Star Schema. I will paste a picture of the results where the lines stop for both ytd measures based on the most recent data for current year. I hope this solves your question. Thanks..

https://drive.google.com/file/d/16SvlMII1FmZIEDS_N2ilBjyt4Imbb_6J/view?usp=sharing

Re: Year to date, compare to last year, per category

ImPalaBI — Wed, 06 Apr 2022 13:37:20 GMT

Thanks for the reply.
I´m not sure if I´m explaning this right, the chart looks good, but I dont need the categories as a filter or slicer, I need them to add them as Legend on the chart/visual.

Re: Year to date, compare to last year, per category

Whitewater100 — Wed, 06 Apr 2022 13:55:58 GMT

Hi:

OK, here is an update showing you two ways to present, including the sales cat as legend. I hope this solves the question for you.

https://drive.google.com/file/d/16SvlMII1FmZIEDS_N2ilBjyt4Imbb_6J/view?usp=sharing

Re: Year to date, compare to last year, per category

eidev12 — Fri, 18 Aug 2023 08:14:27 GMT

I have facing same issue

in my case i have different categories column and i would like to compare this year date range with last year