New Measure calculated based on week number

[Anonymous]

Hi,  I'm new to Power BI and still trying to get my head round some of the DAX language.

Basically im trying to create a simple sales uplift model where I can apply an % uplift to the weeks greater than 'x' week.

So for example.  My actuals are in weeks 1 to 10 and my forecast is in weeks 11 onwards.  I would like my "ADJ_SALES" column to show the "SALES" figure upto week 10 but then apply the % uplift only to weeks 11 onwards.

Any help would be much appreciated .  Thanks

[v-huizhn-msft]

Hi @Anonymous，

We would like to help you solve the problem. But I still have some information needed to confirm.

In you resource data, you only have the data between weeks1 and weeks10, right? “my forecast is in weeks 11 onwards” , you want to calculate the date after weeks10 using measure?

>> I would like my "ADJ_SALES" column to show the "SALES" figure upto week 10 but then apply the % uplift only to weeks 11 onwards.

Could you please share describe it more clearly, or you post an example for further analysis?

Best Regards,
Angelia

[Anonymous]

Thank you everyone for the replies so far.

Angelia,  I will try to describe more clearly....

My aim is to create a visual representation of a sales uplift model where essentially I would use a multiplier eg 5% to uplift my weekly forecast.   I would want my 5% only applied to my forecast numbers (my forecast numbers are actually based on prior-year actuals.)

So, if I had actuals upto week 10 in my table I would want the "measure/column/calculated field" to show the actuals upto week 10 but for week 11 onwards I would want the measure to show the "forecast" figure multiplied by 105%  (or any other uplift% I choose)

What I would really like is to be able to have a selection of uplifts% I could use to apply to the model based on a parameter selection.

I have the access to create tables within the database so if it's something I need to do on that side then fine but I assumed it would be best to create this measure within power BI itself

Again thanks for taking the time to reply,  hopefully I can get my head around this great tool soon

[v-huizhn-msft]

Hi @Anonymous,

You have calculated the percentage in another column(eg, 5%), right? If it is, you can create another calculated column using the formula below.

Test = IF([Week] <11,[percentage column],1+[percentage column])

Best Regards,
Angelia

[Bokazoit]

How about creating a calculated column instead of a measure?

[Anonymous]

OK sure, but any ideas on the syntax I would use for that? Thanks

[Bokazoit]

Depends on where You refresh data from. If it is a SQL query I would add something like:

case when DATEPART(ISO_WEEK, getdate()) > Yourtable.Week then ADJ_SALES else SALES end

[Bokazoit]

In DAX:

Test = IF([Week] > WEEKNUM(TODAY());[ADJ_SALES];[SALES])