Year to date, compare to last year, per category

[ImPalaBI]

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.

[eidev12]

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 

[Whitewater100]

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 

[ImPalaBI]

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

[v-janeyg-msft]

@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

[ImPalaBI]

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.

[Whitewater100]

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 

[ImPalaBI]

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.

[Whitewater100]

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 

[Whitewater100]

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

[johnt75]

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.

[ImPalaBI]

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:

[Whitewater100]

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