Sum of month Last year, per date

[Sohan10]

Hi
We are in the process of changing BI-system to PowerBI, and are therefore recreating our old reports.
One visual that needs recreation, shows revenue generated each day, and a target set as the average created per day, in the same month last year.
So for that I need to get the sum of the entire month (not MTD) last year, based on the date this year. Making it an average is no problem.
So for each day in july I need the sum of july last year, when we then enter august, the dates in july should still use july, and the dates in august then needs the sum of august last year. 

I can't seem to find any date-functions for this or any solutions online. Hope someone can help. Below is the visual I wish to recreate.

[DimaMD]

Hi @Sohan10  try it measure 

var min_date = Min('dates'[Date])
var min_date_LY = DATE(
    YEAR(
        MIN(
            'dates'[Date]))-1, 
            MONTH(min_date),
             DAY(min_date))
var max_date = Max('dates'[Date])
var max_date_LY = DATE(YEAR(MAX('dates'[Date]))-1, MONTH(max_date), DAY(max_date))
Return
CALCULATE([measure], DATESBETWEEN('Календар'[Date], min_date_LY, max_date_LY))

[Sohan10]

This gave me the same result as the first suggestion from 123abc. 
Reading it, I didn't think it would be giving me the result it did. 
Fortunalty the question has been answers, thanks for your respons.

[123abc]

To recreate a visual in Power BI that shows the sum of revenue for each day in the current month and the average for the same days in the previous year, you can use DAX (Data Analysis Expressions) measures to achieve this. Here's a step-by-step guide on how to create this visual:

1. Create a Date Table: If you haven't already, create a date table in your Power BI model. This table should contain a list of dates covering the range you need, with a relationship to your main data table.

2. Create a Measure for Sum of Revenue: Create a DAX measure to calculate the sum of revenue for the current year's dates. Assuming your revenue column is named "Revenue," the measure would look something like this:

Total Revenue = SUM('YourDataTableName'[Revenue])

Create a Measure for Sum of Revenue Last Year: Now, you need to create a measure to calculate the sum of revenue for the same month and day in the previous year. You can use the SAMEPERIODLASTYEAR function to achieve this. Here's an example of how to do it:

Total Revenue Last Year =
VAR CurrentDate = MAX('YourDateTableName'[Date])
VAR SameDateLastYear = SAMEPERIODLASTYEAR(CurrentDate, 'YourDateTableName'[Date])
RETURN
CALCULATE([Total Revenue], SameDateLastYear)

1. This measure calculates the sum of revenue for the same date in the previous year.

2. Create a Measure for the Average of Previous Year's Revenue: Now, create a measure to calculate the average of revenue for the same day in the previous year. Here's how you can do it:

Avg Revenue Last Year = DIVIDE([Total Revenue Last Year], COUNTROWS('YourDateTableName'))

1. This measure divides the total revenue from the previous year by the number of days in the month.

2. Create a Visual: Now that you have the necessary measures, you can create a visual. For your visual, use the Date from your date table on the x-axis and place the "Total Revenue" and "Avg Revenue Last Year" measures on the values section of your visual. It should display the daily revenue for the current year and the average for the same dates in the previous year.

Remember to adjust the table and column names in the DAX measures to match your specific dataset. Once set up, your visual will dynamically show the sum of revenue for each day in the current month and the average for the same days in the previous year as you navigate through different months.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Sohan10]

The visual does not show current month, but shows the last 30 days, meaning that it can and will show different months at the same time. The average is therefore not of the same period last year, but of the month of each date shown in the last 30 days, last year.

I have a complete dataset, with a datetable and revenue tables. 

I have tried your suggested DAX measures, they unfortunatly do not give the result I'm after.

Sameperiodlastyear only takes one input, so that does not work when taking your suggestion, and as the input needs to be a datetable, I removed the CurrentDate, as that did not have the correct input format.

This might be what ended up making it all not work.

But as I can gather from the results, and as I would expect, this will then return the revenue from each day individually last year, 4/10/2023 = 4/10/2022, and subsequently only diving by one row. 

But I have ended up with this result, with the target being the line.

[123abc]

I apologize for the confusion. If you need to calculate the average for the same period in the previous year for a rolling 30-day window and display it in a visual like the one you've shown, you can use the following DAX measure to achieve this:

AvgRevenueLastYearRolling30Days =
VAR CurrentDate = MAX('YourTable'[Date]) // Replace 'YourTable' with your actual table name
VAR LastYearStartDate = CurrentDate - 365
VAR LastYearEndDate = CurrentDate - 335 // 365 - 30 days
RETURN
CALCULATE(
AVERAGE('YourTable'[Revenue]),
FILTER(
ALL('YourTable'),
'YourTable'[Date] >= LastYearStartDate && 'YourTable'[Date] <= LastYearEndDate
)
)

This measure calculates the average revenue for the same 30-day period in the previous year for each date based on your visual's selection.

Here's how to use this measure:

1. Create a new measure in your Power BI model as before.

2. Copy and paste the DAX formula provided above, and replace 'YourTable' with your actual table name.

3. Add the measure AvgRevenueLastYearRolling30Days to your visual to display the rolling 30-day average revenue from the same period in the previous year.

This measure should give you the correct result for a rolling 30-day window, as described in your visual, and it will adjust as you select different dates.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Sohan10]

There seems to still be some confusion. I am not looking for a target as an average of the rolling 30 days. I am looking for an average of the month of each date, last year. 

So today it would show created each day from 19th of september 2023 to 18th of October 2023. 

Each day in september from 19th - 30th would have a target that is the average revenue pr day of september 2022 (1st to 30th), where as each day in october has a target that is the average revenue pr day of october 2022 (1st to 31st).

[123abc · Accepted solution]

I apologize for the misunderstanding. To achieve the average revenue per day for the same month last year for the last 30 days, you can create a DAX measure as follows:

RevenueLastYearSameMonthAvg =
VAR CurrentDate = MAX('YourDateTable'[Date])
VAR LastYearDate = DATEADD(CurrentDate, -1, YEAR)
VAR StartDate = DATE(YEAR(LastYearDate), MONTH(CurrentDate), 1)
VAR EndDate = EOMONTH(LastYearDate, 0)
VAR TotalRevenueLastYear =
CALCULATE(
SUM('YourRevenueTable'[Revenue]),
'YourDateTable'[Date] >= StartDate,
'YourDateTable'[Date] <= EndDate
)
VAR DaysInMonth = DAY(EOMONTH(LastYearDate, 0))
RETURN
DIVIDE(TotalRevenueLastYear, DaysInMonth)

This measure calculates the average revenue per day for the same month last year based on the last 30 days, where the average for each day is the total revenue for the corresponding month last year divided by the number of days in that month.

You can use this measure in your visual to show the average revenue for each day in the last 30 days, where the average is based on the same month last year. Replace 'YourDateTable' and 'YourRevenueTable' with the actual names of your tables.

This should give you the desired result where each day in the last 30 days has a target that represents the average revenue per day of the same month last year.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Sohan10]

Thank you, that worked and gave me the result i wanted.