Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Bindi_Baji
Frequent Visitor

Chart with last 7 days variance

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 NoCustomerProductCodeDescriptionOrderTypeOrderDateOrderQty
10035CustAAAA109Product AA109Forecast19/11/20222
10035CustAAAA109Product AA109Confirmed Order19/11/20224
10035CustAAAA109Product AA109Forecast20/11/20223
10035CustAAAA109Product AA109Confirmed Order20/11/20225
10035CustAAAA109Product AA109Forecast21/11/20226
10035CustAAAA109Product AA109Confirmed Order21/11/20228
10035CustAAAA109Product AA109Forecast22/11/20225
10035CustAAAA109Product AA109Confirmed Order22/11/20227
10035CustAAAA109Product AA109Forecast23/11/20229
10035CustAAAA109Product AA109Confirmed Order23/11/202212
10035CustAAAA109Product AA109Confirmed Order24/11/20223
10035CustAAAA109Product AA109Forecast25/11/20222
10035CustAAAA109Product AA109Confirmed Order25/11/20227
10035CustAAAA109Product AA109Forecast26/11/20223
10035CustAAAA109Product AA109Confirmed Order26/11/20228
10035CustAAAA109Product AA109Forecast27/11/20221
10035CustAAAA109Product AA109Confirmed Order27/11/20225
10035CustAAAA109Product AA109Forecast28/11/20222
10035CustAAAA109Product AA109Confirmed Order28/11/20225

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!

1 ACCEPTED 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:

model.jpg

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:

result.jpg

 

 Sample PBIX file attached

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

4 REPLIES 4
PaulDBrown
Community Champion
Community Champion

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)"





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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:

model.jpg

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:

result.jpg

 

 Sample PBIX file attached

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Bindi_Baji
Frequent Visitor

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)

Bindi_Baji_1-1669544886994.png

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.