It sounds like you're trying to create a measure that calculates the sum of `SALES_QUANTITY` for the current fiscal year and the previous fiscal year, and have it be filtered by the slicers for `FISCAL_YEAR`, `FISCAL_QUARTER`, and `LOCATION_NAME`.
One way to achieve this is to create two measures: one for the current fiscal year and one for the previous fiscal year. The measure for the current fiscal year can be created using the `CALCULATE` function, which allows you to perform calculations within a specific filter context. Here's an example of what the measure for the current fiscal year might look like:
```
Current FY Sales =
CALCULATE(
SUM(SALES[SALES_QUANTITY]),
USERELATIONSHIP(SALES[FULFILL_DATE], DATE[DATE_DT])
)
```
This measure calculates the sum of `SALES_QUANTITY` from the `SALES` table, and uses the `USERELATIONSHIP` function to apply the relationship between the `FULFILL_DATE` column in the `SALES` table and the `DATE_DT` column in the `DATE` table.
The measure for the previous fiscal year can be created in a similar way, but with an additional filter to only include data from the previous fiscal year. Here's an example of what this measure might look like:
```
Previous FY Sales =
CALCULATE(
SUM(SALES[SALES_QUANTITY]),
USERELATIONSHIP(SALES[FULFILL_DATE], DATE[DATE_DT]),
DATEADD(DATE[DATE_DT], -1, YEAR)
)
```
This measure is similar to the measure for the current fiscal year, but it includes an additional filter using the `DATEADD` function to shift the dates in the `DATE_DT` column back by one year. This will effectively filter the data to only include sales from the previous fiscal year.
Once you have these two measures, you can use them in a visual or table to compare sales between the current and previous fiscal years. These measures should also respond to any slicers you have on your report page, allowing you to filter the data by `FISCAL_YEAR`, `FISCAL_QUARTER`, and `LOCATION_NAME`.
I hope this helps! Let me know if you have any questions or if there's anything else I can do to assist you. 😊
