Help with creating a measure to calculate sales from the previous year to the one selected in a segmenter

[Syndicate_Admin]

Good night.

I've been trying to solve a problem that seems simple for 2 days but I'm not able to do it.

I have a table 00 with a column "Location"
I have a table 01 with a column called "Date" whose values are date and time and are repeated values, they are not unique values.
I have table 02 with a "Price" column and another column called "Product Name".

All tables are related in a star model.

I have a measure called "Total Sales" which is the sum of the "Price" values in table 02.

On one sheet I have a card with the value "Total sales" and three segmenters.
One "Date" in hierarchy to only express years.
Another segmenter contains "Product Name".
The last segmenter contains "Location".

I need to create a new measure to incorporate into a card that indicates the total sales of the previous year, I will call it "TotalsalesPreviousYear", this value will be the value of the previous year, so if in the segmenter hierarchized by year "Recording Date" I choose a year, "Totalsales" represents that value and the new measure "TotalsalesPreviousYear" has to represent the value of the previous year to the selected one.

If I apply any slicer, the amount represented will change based on the slicers chosen.

I've used a lot of codes and it never works quite right.

With this code I provide it seems to work but the values when I apply the Date segmenter are not correct, if they are if I use the Date segmenter combined with any other, but the Date segmenter alone does not provide the correct values.

TotalsalesPreviousYear =
VAR YearSelected = MAXX(ALLSELECTED('01'), YEAR('01'[DATE]))
RETURN
CALCULATE YOURSELF
[Totalventas],
FILTER(
ALL('01'),
YEAR('01'[DATE]) = SelectedYear - 1
),
KEEPFILTERS(VALUES('00'[Location]))
)

Can anyone help me?
Thank you very much in advance.

[v-echaithra]

Hi @Syndicate_Admin ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra]

Hi @Syndicate_Admin ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra]

 Hi @Syndicate_Admin ,

Glad that your query got resolved and If our response addressed by the community member for your query, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.

Thank you for being a part of the Microsoft Fabric Community Forum!

[Syndicate_Admin]

Thanks for the answer.

I modified the model and everything was simpler.

I added a "Year" column based on the "Date" column and then with three simple measurements the desired result was obtained.

I choose the selected year:

Yearselcted = SELECTEDVALUE(01'[Year])

Selected year:

TotalventasAnoSelected =

CALCULATE(

SUM('02Ventas'[Quantity]),

01'[Year] = SELECTEDVALUE(01'[Year])

)

Previous year:

TotalventasAnoAnterior =

CALCULATE(

SUM('02Ventas'[Quantity]),

01'[Year] = SELECTEDVALUE(01'[Year]) - 1

)

Very easy and simple to modify the model by adding the "Year" column to be able to choose previous years.

Thanks for the help.

[HarishKM]

@Syndicate_Admin Hey,
Try below measure 

TotalsalesPreviousYear =
VAR SelectedYear = YEAR(MAX('01'[Date])) -- Get the selected year from the Date slicer
RETURN
CALCULATE(
[Total Sales], -- Your Total Sales measure
FILTER(
ALL('01'), -- Remove all filters from the Date table (so you can isolate the previous year)
YEAR('01'[Date]) = SelectedYear - 1 -- Filter for the previous year
),
KEEPFILTERS(VALUES('00'[Location])), -- Keep any filters applied on Location
KEEPFILTERS(VALUES('02'[Product Name])) -- Keep any filters applied on Product Name
)

Thanks
Harish M
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query