Measures calculated based on slicer

[Anonymous]

Hi, 

I am trying to create a report which is for my finance team. I need to be able to project for the rest of the years income. To do this they take last weeks income and multiply it by the remaining weeks in the year and add it to the weeks already gone. E.G.

Week 3 = week 1 + week 2 + week 3 + (week 3 * (52-3))

Week 4 = week 1 + week 2 + week 3 + week 4 + (week 4 *(52-4)) etc. 

I have created measures for each of the weeks I just need to be able to make them change and automatically calculate based on the week number selection I make in a slicer. 

Many thanks for your help!

Josh 

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

We can use the following steps to meet your requirement.

1. Create a week column, weekFY column, a weekname column and a fiscal year column.

week = WEEKNUM('table'[Date],1)

WeeKFY = 
VAR WeekStartinFY =
    WEEKNUM ( DATE ( YEAR('table'[Date]), 4, 1 ), 1 )
var max_date = MAX('table'[week])
RETURN
    IF (
        'table'[week] < WeekStartinFY,
        max_date -WeekStartinFY + 'table'[week] ,
        'table'[week] - WeekStartinFY +1      
)         

week name = CONCATENATE("Week ",'table'[WeeKFY])

Fiscal Year = 
IF('table'[Date]<DATE(YEAR('table'[Date]),3,31),YEAR('table'[Date]),YEAR('table'[Date])+1)

2. Then we can create a measure,

Measure = 
var Date__ = MIN('table'[WeeKFY])
var year__ = MIN('table'[Fiscal Year])
var week_value = CALCULATE(SUM('table'[value]),FILTER(ALL('table'),'table'[WeeKFY]=Date__ && 'table'[Fiscal Year] = year__))
var grand_total = CALCULATE(SUM('table'[value]),FILTER(ALL('table'),'table'[WeeKFY]<=Date__ && 'table'[Fiscal Year] = year__))
return
grand_total + week_value * (52-Date__)

The result like this,

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danno]

Hi, 

could you just confirm what type of calendar you are using, is it fiscal or a regular calendar? 

[Anonymous]

hi @danno , 

I am using a fiscal calendar but our weeks start from 30th March til 5th April is week 1 etc.

Thanks

[danno]

I would start with this articel to make sure you are suing the right logic for week based time intelligence. 

https://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/

There are some patterns in there for doing week based YTD calculations. 

i would create separate measures for the previous week, YTD and then combine those to get your projection.