cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Total Sales LY

Hi

I have a filter on a page for dates and are showing sales and sales LY in a matrix. Now I want to ass sales LY full month as another measure, how can I do that?

E.g.

Measure 1 (Sales) =  sum('Sales'[Amount]) - Sales 1-12 of April 2024

Measure 2 (Sales LY) = CALCULATE([Sales],SAMEPERIODLASTYEAR('Date'[Date])) - Sales 1-12 of April 2023

Measure 3 (Sales LY Full Month) = ??? - Sales 1-30 of april 2023

4 REPLIES 4
Super User

To create a measure that calculates sales for the full last year month, regardless of the current filter context of partial month data in your report, you can use the `DATESYTD` function in combination with `SAMEPERIODLASTYEAR` and other DAX functions to adjust the date range. The goal is to ensure that the measure reflects sales for the entire month of the previous year corresponding to the currently selected month in the current year.

Here's how you can create your "Sales LY Full Month" measure:

### Step 1: Set Up Necessary Measures

First, ensure you have the basic measures set up as you mentioned:

- **Measure 1 (Sales):** This is straightforward as you've outlined:

```DAX
Sales = SUM('Sales'[Amount])
```

- **Measure 2 (Sales LY):** As you described, adjusting for the same period last year:

```DAX
Sales LY = CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date]))
```

### Step 2: Create the Sales LY Full Month Measure

To calculate the sales for the entire last year month, you will use a combination of `DATESBETWEEN`, `SAMEPERIODLASTYEAR`, and `EDATE` to ensure the full month is covered:

```DAX
Sales LY Full Month =
CALCULATE(
[Sales],
DATESBETWEEN(
'Date'[Date],
STARTOFMONTH(SAMEPERIODLASTYEAR('Date'[Date])),
ENDOFMONTH(SAMEPERIODLASTYEAR('Date'[Date]))
)
)
```

### Explanation:

- **`CALCULATE([Sales], ...)`**: This changes the context in which the `[Sales]` measure is calculated.
- **`DATESBETWEEN('Date'[Date], ...)`**: This function defines a specific date range for the calculation.
- **`STARTOFMONTH(SAMEPERIODLASTYEAR('Date'[Date]))`** and **`ENDOFMONTH(SAMEPERIODLASTYEAR('Date'[Date]))`**: These functions find the first and last day of the month from the same period last year, ensuring that the calculation covers the entire month, regardless of the current selection.

### Step 3: Use the Measure in Your Reports

Add this new measure to your matrix or any other visualizations where you need to compare the current month's sales to the last year's full month's sales. It will automatically adjust to reflect the full previous year month corresponding to the selected (or filtered) current year month.

- **Review Date Relationships**: Ensure that your date table is correctly set up and related to your sales data for these calculations to work correctly.
- **Handling Incomplete Months**: If your data includes incomplete months (e.g., for the most recent month), this measure still correctly compares full months from the previous year.
- **Performance**: Keep an eye on the performance when using time intelligence functions, especially over large datasets or across complex models.

This DAX setup should effectively give you the comparison you're looking for between sales in the current period and the corresponding full month in the previous year, making it easy to analyze trends and performance year-over-year.

Helper I
Helper I

Hi
How do I incorporate SAMEPERIODLASTYEAR in above example?

If I use your current proposal I will recieve current year sale

Continued Contributor

You should use the PREVIOUSYEAR DAX function. Here is an example:

``````Sales LY Full Month =
CALCULATE(
[Sales],
PREVIOUSYEAR(Dates[Date])
)``````

If you are still stuck, kindly provide the following:

• A sample dataset
• A screenshot/dataset of the expected solution

Best Regards,
Udit

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.