Projected Monthly Sales

[elcamino]

Hello everyone,

I need to know the projected monthly sales based on the sales of the last 2 years.

However, when i use this measure:

Projected Monthly Sales =
VAR CurrentDate = MAX(DIM_DATE[Date]) -- Current date from the DIM_DATE table (filter context)
VAR CurrentMonth = MONTH(CurrentDate) -- Get current month
VAR CurrentYear = YEAR(CurrentDate) -- Get current year
VAR CurrentDay = DAY(CurrentDate) -- Get current day of the month
VAR DaysInMonth = DAY(EOMONTH(CurrentDate, 0)) -- Get total days in the current month
VAR RemainingDays = DaysInMonth - CurrentDay -- Calculate the remaining days in the current month

-- 1. Calculate current MTD sales (sales from the start of the month until today)
VAR CurrentMTDSales =
CALCULATE(
SUM(Table1[Sales]),
FILTER(
ALL(DIM_DATE),
DIM_DATE[Date] >= DATE(CurrentYear, CurrentMonth, 1) &&
DIM_DATE[Date] <= CurrentDate
)
)

-- 2. Calculate average daily sales for the same period last year
VAR LastYearAvg =
CALCULATE(
DIVIDE(
SUM(Table1[Sales]),
COUNTROWS(VALUES(DIM_DATE[Date])) -- Calculate daily average for last year
),
FILTER(
ALL(DIM_DATE),
MONTH(DIM_DATE[Date]) = CurrentMonth &&
DAY(DIM_DATE[Date]) > CurrentDay &&
DAY(DIM_DATE[Date]) <= DaysInMonth &&
YEAR(DIM_DATE[Date]) = CurrentYear - 1 -- Filter for last year
)
)

-- 3. Calculate average daily sales for the same period two years ago
VAR TwoYearsAgoAvg =
CALCULATE(
DIVIDE(
SUM(Table1[Sales]),
COUNTROWS(VALUES(DIM_DATE[Date])) -- Calculate daily average for two years ago
),
FILTER(
ALL(DIM_DATE),
MONTH(DIM_DATE[Date]) = CurrentMonth &&
DAY(DIM_DATE[Date]) > CurrentDay &&
DAY(DIM_DATE[Date]) <= DaysInMonth &&
YEAR(DIM_DATE[Date]) = CurrentYear - 2 -- Filter for two years ago
)
)

-- 4. Calculate the combined historical daily average
VAR HistoricalDailyAvg =
IF(
AND(NOT(ISBLANK(LastYearAvg)), NOT(ISBLANK(TwoYearsAgoAvg))),
(LastYearAvg + TwoYearsAgoAvg) / 2, -- Average both years' values
IF(
NOT(ISBLANK(LastYearAvg)),
LastYearAvg, -- Use last year's average if no two-years-ago data
IF(
NOT(ISBLANK(TwoYearsAgoAvg)),
TwoYearsAgoAvg, -- Use two years ago if no last year's data
DIVIDE(CurrentMTDSales, CurrentDay) -- Fallback to current MTD daily average
)
)
)

-- 5. Calculate the projected sales for the remaining days
VAR ProjectedRemainingSales = HistoricalDailyAvg * RemainingDays

-- 6. Final projection: combine actual MTD sales and projected remaining sales
RETURN
CurrentMTDSales + ProjectedRemainingSales


I got the same values for the actual and projected sales which shouldn't be the case because, for instance, in this month, April, we still have 7 days left.

Thanks a lot. 

[v-bmanikante]

Hi @elcamino 

We would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.

If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,

B Manikanteswara Reddy

[v-bmanikante]

Hi @elcamino ,

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,

B Manikanteswara Reddy

[v-bmanikante · Accepted solution]

Hi @elcamino ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Please don't forget to give a "Kudos " – I’d truly appreciate it!

Regards,

B Manikanteswara Reddy

[ajaybabuinturi]

Hi @elcamino, 
Thanks @OktayPamuk80 for your prompt response. It will also works good.
Considering your request you can try to replace below DAX with updated DAX.

-- 2. Calculate average daily sales for the same period last year
VAR LastYearAvg =
CALCULATE(
DIVIDE(
SUM(Table1[Sales]),
COUNTROWS(VALUES(DIM_DATE[Date])) -- Calculate daily average for last year
),
FILTER(
ALL(DIM_DATE),
MONTH(DIM_DATE[Date]) = CurrentMonth &&
DAY(DIM_DATE[Date]) > CurrentDay &&
DAY(DIM_DATE[Date]) <= DaysInMonth &&
YEAR(DIM_DATE[Date]) = CurrentYear - 1 -- Filter for last year
)
)

Modified DAX: 
Last Yr. Avg =  
Var LastYearDates= 
FILTER (
ALL(DIM_DATE),
MONTH(DIM_DATE[Date]) = CurrentMonth &&
DAY(DIM_DATE[Date]) > CurrentDay &&
DAY(DIM_DATE[Date]) <= DaysInMonth &&
YEAR(DIM_DATE[Date]) = CurrentYear - 1 -- Filter for last year
)

Var LastYearAvg = 
DIVIDE(
CALCULATE(SUM(Table1[Sales]), LastYearDates),
COUNTROWS(LastYearDates)
)

RETURN
LastYearAvg

Apply the same change to TwoYearsAvg

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[OktayPamuk80]

Hi, 

you mean something like forcasting? If you use a linechart, you can have forecasting. See "Apply Forecasting" section of this link:

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-analytics-pane

Regards,

Oktay