Forum Discussion
Projected Monthly Sales
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
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.