Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
Newbie to Power BI but intermediate in Excel and VBA so I'm currently getting used to everything with PBI. Enjoying it so far.
I've been asked by my company to put together a forecast data dashboard by my company with a comparision of forecast vs orders.
I've been able to create a card with today's order variance on but I've been asked if I can put a chart in with the previous 7 days variance and this has me stumped.
As we work on delivery days, today's orders are dated tomorrow so the dates I'd need are essentially (Today’s Date)-6 through (Today’s Date)
Example of data in table Forecast_Data_1
Item No | Customer | ProductCode | Description | OrderType | OrderDate | OrderQty |
10035 | CustAA | AA109 | Product AA109 | Forecast | 19/11/2022 | 2 |
10035 | CustAA | AA109 | Product AA109 | Confirmed Order | 19/11/2022 | 4 |
10035 | CustAA | AA109 | Product AA109 | Forecast | 20/11/2022 | 3 |
10035 | CustAA | AA109 | Product AA109 | Confirmed Order | 20/11/2022 | 5 |
10035 | CustAA | AA109 | Product AA109 | Forecast | 21/11/2022 | 6 |
10035 | CustAA | AA109 | Product AA109 | Confirmed Order | 21/11/2022 | 8 |
10035 | CustAA | AA109 | Product AA109 | Forecast | 22/11/2022 | 5 |
10035 | CustAA | AA109 | Product AA109 | Confirmed Order | 22/11/2022 | 7 |
10035 | CustAA | AA109 | Product AA109 | Forecast | 23/11/2022 | 9 |
10035 | CustAA | AA109 | Product AA109 | Confirmed Order | 23/11/2022 | 12 |
10035 | CustAA | AA109 | Product AA109 | Confirmed Order | 24/11/2022 | 3 |
10035 | CustAA | AA109 | Product AA109 | Forecast | 25/11/2022 | 2 |
10035 | CustAA | AA109 | Product AA109 | Confirmed Order | 25/11/2022 | 7 |
10035 | CustAA | AA109 | Product AA109 | Forecast | 26/11/2022 | 3 |
10035 | CustAA | AA109 | Product AA109 | Confirmed Order | 26/11/2022 | 8 |
10035 | CustAA | AA109 | Product AA109 | Forecast | 27/11/2022 | 1 |
10035 | CustAA | AA109 | Product AA109 | Confirmed Order | 27/11/2022 | 5 |
10035 | CustAA | AA109 | Product AA109 | Forecast | 28/11/2022 | 2 |
10035 | CustAA | AA109 | Product AA109 | Confirmed Order | 28/11/2022 | 5 |
Measures I created for the card visuals:
Forecast_Today = FORMAT(CALCULATE(sum(Forecast_Table_1[OrderQty]),Forecast_Table_1[OrderDate]=TODAY()+1,Forecast_Table_1[OrderType]="Forecast"),"#,##0")
Orders_Today = FORMAT(CALCULATE(sum(Forecast_Table_1[OrderQty]),Forecast_Table_1[OrderType]="Confirmed Order",Forecast_Table_1[OrderDate]=TODAY()+1),"#,##0")
Order_Diff = CALCULATE(sum(Forecast_Table_1[OrderQty]),Forecast_Table_1[OrderType] = "Confirmed Order",Forecast_Table_1[OrderDate]=today()+1)-CALCULATE(SUM(Forecast_Table_1[OrderQty]),Forecast_Table_1[OrderType]="Forecast",Forecast_Table_1[OrderDate]=TODAY()+1)
Order_Percent = FORMAT(DIVIDE( [Order_Diff] , [Forecast_Today] , 0) ,"+0.00%;-0.00%;0")
Any help or guidance would be greatly appreciated!
Solved! Go to Solution.
See if this works. First create a date table using the following DAX:
Date Table =
ADDCOLUMNS(
CALENDAR(MIN(Forecast_Table_1[OrderDate]), MAX(Forecast_Table_1[OrderDate])),
"MonthNum", MONTH([date]),
"Month", FORMAT([Date], "MMM"),
"Year", YEAR([Date]))
and an Order Type table:
Order type table =
DISTINCT(Forecast_Table_1[OrderType])
Set up the model as follows:
and use the following measure:
7 Day Variance % =
VAR _minDate =
MAX ( 'Date Table'[Date] ) - 6
VAR _Date =
MAX ( 'Date Table'[Date] )
VAR _7Confirmed =
CALCULATE (
SUM ( Forecast_Table_1[OrderQty] ),
'Order type table'[OrderType] = "Confirmed Order",
DATESBETWEEN ( 'Date Table'[Date], _minDate, _Date )
)
VAR _7Forecast =
CALCULATE (
SUM ( Forecast_Table_1[OrderQty] ),
'Order type table'[OrderType] = "Forecast",
DATESBETWEEN ( 'Date Table'[Date], _minDate, _Date )
)
RETURN
DIVIDE ( _7Confirmed, _7Forecast ) - 1
To get:
Sample PBIX file attached
Proud to be a Super User!
Paul on Linkedin.
Sorry, can you please clarify the variance calculation? In your measures you compute the date TODAY() + 1, but in the variance calculation you talk about "Today’s Date)-6 through (Today’s Date)"
Proud to be a Super User!
Paul on Linkedin.
Hi,
Thanks for your reply.
The variance measure is for card visual for today's orders (tomorrow's date as it is delivery date), which works perfectly for this.
I'm being asked if I can show the same variance % figure for each of the previous 7 days (not including today's order)
Apologies, I just showed it here to show what I have at the moment and if it can be altered.
See if this works. First create a date table using the following DAX:
Date Table =
ADDCOLUMNS(
CALENDAR(MIN(Forecast_Table_1[OrderDate]), MAX(Forecast_Table_1[OrderDate])),
"MonthNum", MONTH([date]),
"Month", FORMAT([Date], "MMM"),
"Year", YEAR([Date]))
and an Order Type table:
Order type table =
DISTINCT(Forecast_Table_1[OrderType])
Set up the model as follows:
and use the following measure:
7 Day Variance % =
VAR _minDate =
MAX ( 'Date Table'[Date] ) - 6
VAR _Date =
MAX ( 'Date Table'[Date] )
VAR _7Confirmed =
CALCULATE (
SUM ( Forecast_Table_1[OrderQty] ),
'Order type table'[OrderType] = "Confirmed Order",
DATESBETWEEN ( 'Date Table'[Date], _minDate, _Date )
)
VAR _7Forecast =
CALCULATE (
SUM ( Forecast_Table_1[OrderQty] ),
'Order type table'[OrderType] = "Forecast",
DATESBETWEEN ( 'Date Table'[Date], _minDate, _Date )
)
RETURN
DIVIDE ( _7Confirmed, _7Forecast ) - 1
To get:
Sample PBIX file attached
Proud to be a Super User!
Paul on Linkedin.
Hi,
Hoping someone can still help.
This is the kind of thing I'm hoping to acheive. (Manipulated one number to show a negative value)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
147 | |
109 | |
107 | |
85 | |
63 |