DAX Sales Projection Measure

DAX
1

I am trying to write a measure that will project my Total Sales (that is measured Monthly) for the rest of the year. I generated the following DAX from the Data Mentor.

Projected Sales Rest of Year = 
VAR CurrentYear = YEAR(TODAY())
VAR TotalSalesYearToDate = CALCULATE([Total Sales], DATESYTD('Dates'[Date]))
VAR TotalDaysYearToDate = COUNTROWS(DATESYTD('Dates'[Date]))
VAR AverageSalesPerDay = DIVIDE(TotalSalesYearToDate, TotalDaysYearToDate)
VAR RemainingDaysInYear = CALCULATE(COUNTROWS('Dates'), 'Dates'[Year] = CurrentYear) - TotalDaysYearToDate
RETURN
    IF(
        RemainingDaysInYear > 0,
        AverageSalesPerDay * RemainingDaysInYear,
        BLANK()
    )

According to the Code Explainer this measure should return the projected Total Sales for the rest of the year as long as there are days left in the year.

The code will only return BLANK

What am I not seeing in the code. Any thoughts would be appreciated.

Guy

1 Like
2

This is just a thought, does your date table have until the last day in your fiscal year?

3

@Keith
My Date table goes through 12/31/2024. We use the calendar year not a fiscal year

4

ok…it was just a thought

5

Got this work finally after tinkering with the different variables. I’m sure there is a much simpler way to do this and I’m open for a better way. In the meantime, I’m getting what Upper Mangement wants.

I created a new Measure to calculate the number of days in the Year so far:

Days to Date =
VAR CurrentYear = YEAR(TODAY())
VAR YearStart = DATE(CurrentYear, 1, 1)
VAR Today = TODAY()
RETURN
DATEDIFF(YearStart, Today, DAY)

This Measure was then substituted into my main Measure to get my projected sales for the rest of 2024, assuming things don’t change to influence it. I came up with the following Measure:

Projected Sales Rest of Year =
VAR CurrentYear = YEAR(TODAY())
VAR TotalSalesYearToDate = CALCULATE([Total Sales], DATESYTD(‘Dates’[Date]))
VAR TotalDaysYearToDate = [Days to Date]
VAR AverageSalesPerDay = DIVIDE(TotalSalesYearToDate, TotalDaysYearToDate)
VAR RemainingDaysInYear = COUNTROWS(FILTER(‘Dates’, AND(‘Dates’[Year] = CurrentYear, ‘Dates’[Date] >= TODAY())))
RETURN
IF(
RemainingDaysInYear > 0,
AverageSalesPerDay * RemainingDaysInYear,
BLANK()
)

Once again, I’m open to any suggestions that will simplify this.

Guy

6

Calculate projected sales for the rest of the current year =
VAR AverageSalesPerDay = DIVIDE(CALCULATE([Total Sales], DATESYTD(‘Dates’[Date])), COUNTROWS(DATESYTD(‘Dates’[Date])))
VAR RemainingDaysInYear = COUNTROWS(FILTER(‘Dates’, ‘Dates’[Year] = YEAR(TODAY()) && ‘Dates’[Date] > MAX(‘Dates’[Date]))

RETURN
IF(
RemainingDaysInYear > 0,
AverageSalesPerDay * RemainingDaysInYear,
BLANK()
)

IMG_8167
IMG_81672732×2048 508 KB

Does this work for your use case?

7

Thanx @SamMcKay but this code only returns a Blank value.

I found something on YouTube that is doing what I need.

Guy