DAX to calculate percentage difference between forecast and average sales

DAX DAX Calculations
1

Experts,

I’m having trouble coming up with the DAX measure to determine the percentage difference between an item/store/date forecast value and the mean of past sales for the item/store combination.

Here’s how the FACT table looks.

ITEM	STORE	STARTDATE	FORECAST	SALES
134569	1012	9/2/2020	blank	0
134569	1012	9/9/2020	blank	0
134569	1012	9/16/2020	blank	2
134569	1012	9/23/2020	blank	15
134569	1012	9/30/2020	1.79	blank
134569	1012	10/7/2020	1.96	blank
134569	1012	10/14/2020	2.13	blank
134569	1012	10/21/2020	25.01	blank
134569	1012	10/28/2020	3.23	blank
134569	1012	11/4/2020	3.36	blank

As you can see, we have both past and future dates within the table (with past data points having SALES populated and future having FORECAST populated). In this situation, I’d like to have a measure that calculates the percentage difference of each future forecast to average sales and the item/store level.

For example, for the startdate of 9/30/20…
(1.79 - (0+0+2+15)/4) / (0+0+2+15)/4
(1.79 - 4.25) / 4.25 = -58%.

Thanks for the assistance.
MK

2

Hi @_mk7,

Thanks for posting your question. Your Sales column looks blank. I am wondering how did you come up with 4.25 Sales on 9/30/2020?

Kind Regards,
Hafiz

3

Same question … please post your dataset, work-in-progress PBIX, and Excel mockup of your desired outcome.
Greg

4

Sorry about that. I’ve updated the original post to be more clear. I’m basically trying to calculate percentage difference between future forecasts and recent sales to use for outlier detection criteria.

EDIT: Fixed parenthesis placement.

For 9/30/20…
(1.79 - (0+0+2+15)/4) / (0+0+2+15)/4
(1.79 - 4.25) / 4.25 = -58%

For 10/7/20…
(1.96 - (0+0+2+15)/4) / (0+0+2+15)/4
(1.96 - 4.25) / 4.25 = -54%

Thanks!

5

Hi @_mk7,

your calculation doesn’t make sense because you not following math operations
(1.79 - (0+0+2+15/4)) / (0+0+2+15/4)
(1.79- (0+0+2+3.75)) /(0+0+2+3.75)

i think what you want is (1.79 - (0+0+2+15)/4) / (0+0+2+15)/4)

Brackets in the wrong place

keith

1 Like
6

Yep, you’re right. I’ve fixed above.

7

Hi @_mk7,

Here you go, I hope it will satisfy your requirements. Please revert back in case of any issues.

Req. Measure = 
VAR currentForcast =
    CALCULATE (
        VALUES ( 'Table'[FORECAST] ),
        FILTER ( 'Table', 'Table'[STARTDATE] = MAX ( 'Table'[STARTDATE] ) )
    )
VAR CountofSales =
    COUNTROWS (
        FILTER (
            ALL ( 'Table' ),
            'Table'[STARTDATE] <= MAX ( 'Table'[STARTDATE] )
                && NOT ( ISBLANK ( 'Table'[SALES] ) )
        )
    )
VAR TotalSale =
    CALCULATE (
        SUM ( 'Table'[SALES] ),
        FILTER ( ALL ( 'Table' ), 'Table'[STARTDATE] <= MAX ( 'Table'[STARTDATE] ) )
    )
VAR ratio = TotalSale / CountofSales
RETURN
    IF (
        HASONEVALUE ( 'Table'[STARTDATE] ) && NOT ( ISBLANK ( currentForcast ) ),
         ( currentForcast - ratio ) / ratio
    )

image

EDNA_forecast calc.pbix (44.5 KB)

2 Likes
8

Hafiz - You are a gentleman and a scholar. That did the trick. Thanks for the assistance!

1 Like
9

@_mk7,

It was an interesting case and I am glad to know that it worked for you. I am just a learner and trying to learn from experts on this forum:slight_smile:

Kind Regards,
Hafiz