Table showing variance by product, week and month

[mb0307]

Hi all,

Thanks you in advance if someone can help me with my query below.  I am new to DAX.

My table example:

PRODUCT	FORECAST WEEK	MONTH	SALES
Clock	Week 1	August	100
Clock	Week 1	September	500
Fan	Week 1	August	200
Fan	Week 1	September	200
Clock	Week 2	August	150
Clock	Week 2	September	400
Fan	Week 2	August	200
Fan	Week 2	September	300

Above table, shows FORECAST WEEK in which sales numbers were projected for August and September.  As we move to different weeks, our forcast figures are changing for each month.

SUMMARY of above table is:

	Week 1		Week 2
PRODUCT	August	September	August	September
Clock	100	500	150	400
Fan	200	200	200	300

Is it possible using DAX to create a table in this visual format please?

RESULT table should show the PRODUCT and difference between forcasted sales for each month:

Week 1 vs Week 2
PRODUCT	August	September
Clock	50	-100
Fan	0	100

Thanks

[mahoneypat]

Here is one way to solve this one.  First thing is to change your Forecast Week column to an integer (text after space delimiter in query editor), so you can subtract 1 from it easily.  Then you can use a measure like this in a matrix visual with Product (rows) and Month (columns).  It calculates the difference between the forecast is the latest week vs. the previous one.

Forecast Difference =
VAR __thisweek =
MAX ( Forecast[FORECAST WEEK] )
VAR __thisweekforecast =
CALCULATE ( SUM ( Forecast[SALES] ), Forecast[FORECAST WEEK] = __thisweek )
VAR __lastweekforecast =
CALCULATE ( SUM ( Forecast[SALES] ), Forecast[FORECAST WEEK] = __thisweek - 1 )
RETURN
__thisweekforecast - __lastweekforecast

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[mb0307]

Thanks Pat.

I have Weeks in number format and logically I think is what I want.  But if a month has no SALES values (i.e. 0) in the selected weeks then it seems to ignore the week filter and calculate all weeks.  Can this be fixed please?

[mahoneypat · Accepted solution]

Not sure exactly what you mean, but something is returning blank somewhere.  You can make the expression more robust with the following change

Forecast Difference =
VAR __thisweek =
    MAX ( Forecast[FORECAST WEEK] )
VAR __thisweekforecast =
    CALCULATE ( SUM ( Forecast[SALES] ), Forecast[FORECAST WEEK] = __thisweek )
VAR __lastweekforecast =
    CALCULATE ( SUM ( Forecast[SALES] ), Forecast[FORECAST WEEK] = __thisweek - 1 )
RETURN
    IF (
        OR ( ISBLANK ( __thisweekforecast ), ISBLANK ( __lastweekforecast ) ),
        BLANK (),
        __thisweekforecast - __lastweekforecast
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat