IF statement for forecasting

DAX DAX Calculations
1

Hi
I have a dataset of contracts that have varying end dates throughout the year and I want to calculate sales value into the future based on various parameters.
I’ve transformed the contract data into a row per month with salesvalue and then forecasted out to 31/12/2025 flagging whether the month is part of an existing contract or whether it is a forecast amount ([Forecast?])

So in this scenario the actual contract ended on 30/10/2021 therefore 1/11/2021 is considered a forecast whilst 1/10/2021 isn’t.

image
image717×167 4.09 KB

I have two what if parameters Pricing Scenario and Attrition Scenario

Based on the [Forecast?] I want to get a total by customer/contract for a given year where if [Forecast?] = N then use the [Monthly Revenue] otherwise [Monthly Revenue]Pricing Scenario less [Monthly Revenue][Attrition Scenaro]

I’ve tried various If statements / calculated columns etc but can’t get this logic to work

I’ve attached the pbix file

For eg
If Pricing Scenario = 10% and Attrition Scenario = 5% then I would expect

Line 3 to = £366.67
Line 5 to = £403.33 - 18.33 result £385

Forecasting 2022 Test - Copy.pbix (190.0 KB)

As always any help greatly appreciated.

2

Anyone help? If this were excel it would be a very simple if statement
DAX must be able to do this very simply as well I’m just not getting it to work :pensive:

3

Hi @AliB,

I’ve simplified your Total sales measure and created the forecasted one.
image

image

Here is the PBIX file.
Forecasting 2022 Test - Copy.pbix (189.7 KB)

I hope this will be helpfull.

Best regards,
JBocher

3 Likes
4

Thank you - thats great :smiley:

For my understanding Is the selectedvalue performing the same function of an iterating function in this case ie it’s going through the table and each row is deemed selected and therefore evaluated?

5

Yes it is evaluated for each row and gives a results if there is only one value

Here is the documentation about this function

Best regards,
JBocher

1 Like
6

Thanks. It looks like this will only work if I have the [Forecast?] field in the visual?

image
image932×495 138 KB

The result I needed was by customer and by contract

7

Hi @AliB,

You’re right my measure is not complete to answer to your problem.

In fact, the problem is not about the field [Forecast?].
Even if this field is missing from the table, the measure will work.

The problem is about the total of the table that isn’t well calculated.
To correct it, I’ve changed a little the formula.

image

image
image704×241 6.63 KB

With this new measure the total is now correct.
image

Here is the full solution
Forecasting 2022 Test - Copy.pbix (194.8 KB)

Hope it will help you :slight_smile:

Best regards,
JBocher

8

Thanks

Thanks to Harsh the following seemed to work - which is different to yours.
What is the ISINSCOPE adding? Is your version more robust than this one or are they both doing exactly the same thing?
Thanks for your help so far
Total Forecast - Totals =
SUMX(
SUMMARIZE(
ContractDataTest ,
ContractDataTest[new_contractno] ,
ContractDataTest[Forecast Out] ,
ContractDataTest[Forecast?] ,
ContractDataTest[Forecast Month] ,
“@Totals” ,
[Total Forecast] ) ,
[@Totals]
)

9

Hi @AliB,

Both measure are doing the same thing.
The only difference is that Harsh apply his formula every time, whereas I apply my formula only for the totals and I keep the initial measure for the rows.

So ISINSCOPE allow me to see if in the context I have a filter on [Forecast Month] and in the total it is not the case :slight_smile:

Both measures will work.

Best regards,
JBocher

1 Like
10

Thank you. That’s great