DAX Advice - Calculating forecast changes when using a Custom Date Range

[VizDatMat]

Hello fellow Power BI enthusiasts! I would hugely appreciate some DAX advice please!

I have a set of budget figures in 'Main Table', but these numbers will be changing throughout the year, and the changes are logged through the table 'Changes'. 

I'm looking for a way to retrieve the changes and show the adjusted figure in a new column (or measure) next to the budgeted figure - how's best to go about this when using a custom date range?

The custom date range is based on week names, I have added an index number to each week name but looking for some guidance on the best way to connect the 2 tables and the DAX needed to generate the adjusted figure.

PBIX file and underlying spreadsheet here

Thanks in advance!

[jdbuchanan71]

Hello @VizDatMat 

We need to create some tables that we can use to join together the two main tables (Forecast and Change).  We can use the data from the main tables to create these attribute tables.

• Customers
• Products
• Weeks

The we write a couple measures to get

• Original Forecats
• Forecast Change
• New Forecast

And using the attribute tables and the measures we can create the visual.

I have uploaded my updated version of your file here https://www.dropbox.com/s/wlsxo9wizudf9z2/Custom%20Date%20Range.pbix?dl=0 so you can see the measures and tables I used.

[VizDatMat]

Hi @jdbuchanan71

Thank you that's awesome! I need some time to get my head around this, but I can't thank you enough!

It's occurred to me that I have 2 other aspects to build into this and I wonder if you have any advice on this too please?

1, I've have a log of actual sales I'd like to bring in and when that date in time has passed I'd like to switch out the forecast figure and instead display the actual figure in the same column (if there were no sales somehow it needs to show a 0). How's best to achieve this? I have just added the actual sales table to the file and re-uploaded it, called 'Custom Date Range with Actuals' (folder link)

2, Sometimes a product may get cancelled by a customer, or just pulled from production altogether - can you advise the best way of logging/handling this so the data is removed altogether from the table?

Again huge thanks for your help so far!

[jdbuchanan71]

For your first question, we just need to join the new table into our existing attribute tables.

Add a measure to sum the actual amount

Actual Amount = SUM ( Actual[Actual] )

We add a column to the Weeks table to check for the existence of Actuals

Week Has Actual = Weeks[Week Number] <= MAX ( Actual[Week Number] )

That column can be used to calculate a forecast amount on the future weeks only.

Future Forecast = CALCULATE ( [New Forecast], Weeks[Week Has Actual] = FALSE )

Then a measure to combine the actual and future forecast

Actual / Forecast = [Actual Amount] + [Future Forecast]

Since our [Future Forecast] only calculates when we don't have actuals it will give us the correct amount.

For your second question, I would say a table that holds the cancelled products would be best.  Then we add a column to the main products table that just checks for the product in the cancelled list and flags it.  You can use that status column as a filter in your report.

I have updated my sample file here Custom Date Range with actuals.pbix

[VizDatMat]

Hi @jdbuchanan71 thanks again for the explanation and updated file - again, awesome stuff! 

The cancelled table solution looks great, sometimes it's only 1 customer that cancels a product, not all customers - is there a way to build this into the Product Status table?

Ok there's one last element on my wish list, which is to include the product pricing. The prices differ by customer and throughout time too. I've added a pricing data table to the pbix file, now called 'Customer Date Range with pricing' in the online folder.. We'd need to multiply the forecast/actual figures by the pricing data to get the 'Revenue'. I've been trying to follow what you've done and give it a go myself but to no avail - what would you suggest?

I'm so grateful of your help with this, I'm keen to learn more on the modelling side of things your explanations and seeing what you've done in the file is just excellent such help to me (and I'm sure others too!). I'm hoping with time I'll get there!

[jdbuchanan71]

@VizDatMat Will the real pricing table have all customer product combinations at least once for the earliest week and if not what multiplier would you use?  The sample pricing doesn't have any data for week 53 (so we can't use the earliest from the pricing table), product F318 or Customer 3.

You should take a look at the Intro to DAX from SQLBI, it's free and a good place to start.

https://www.sqlbi.com/p/introducing-dax-video-course/

[VizDatMat]

Hi, yes just to confirm the real pricing table does have all customer product combinations for the earliest week.

Thanks for the link, that course looks ideal.

[jdbuchanan71 · Accepted solution]

@VizDatMat 

For the pricing calcs, you would join your pricing table into the attribute tables, just like we did for Main, Changes, and Actual.

Then we need a couple measures, one to calculate the price to use based on the changing time and another to multiply the price * forecast for each customer / product / week

Pricing = 
VAR CurrentWeek = SELECTEDVALUE(Weeks[Week Number] )
VAR PriceWeek = 
    CALCULATE ( 
        MAX ( Pricing[Week ID] ) , 
        ALLEXCEPT(  Pricing, Customers, Products ), 
        Pricing[Week ID] <= CurrentWeek 
    )
RETURN
CALCULATE (
    SUM ( Pricing[Customer Price] ), 
    ALLEXCEPT( Pricing, Customers, Products ), 
    Pricing[Week ID] = PriceWeek 
)

Forecast with Pricing = 
VAR TheTable = 
CROSSJOIN(
    DISTINCT(Customers[Customer]),
    DISTINCT(Products[Product Code]),
    DISTINCT(Weeks[Week Number])
)
RETURN 
SUMX ( TheTable ,[Actual / Forecast] * [Pricing] )

I have updated the PBIX file here: Custom Date Range with pricing.pbix

For the cancelled products

• Can an entire product be cancelled in some cases but in other cases it is only cancelled for some customers?
• Can they be cancelled at different times for different customers?
• If it is cancelled at a certain time do you want to see the actual / forcast before that time, for that customer, but not after?

First though, you should do some studying and work on solving that one yourself.  I worry at this point you have a model and don't really know how any of it works so would not be able to update it or fix any problem.

[VizDatMat]

Hi @jdbuchanan71, I totally agree with your last point. I will go and study the model and see if I can resolve it myself.

Thanks again for the help so far