Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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?
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
93 | |
83 | |
77 | |
74 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |