Calculate value for one month but have the value for each month

[Snip]

Hi 

I have data for 13 months and i have a date table and a data table. They are connected by date keyes. 

I then have a slicer where i choose the last 12 weeks. I would then like to calculate the sum of sales in week 1 and calculate the ratio for eacg sale in the other 11 week. 

I calculate the sale for week 1 as 

Salesweek1 = 

SUMX(

FILTER( KEEPFILTERS(Dim_Date) ,

Dim_Date[Is12WeeksAgo] = 1 ),

[Total Sales])

But this give back a value for all the weeks in data and not only the 12 last weeks.

I found a example of it in Microsoft page for the definition of calculate table. Which show what i need as output but it is not possible for me. 

Can anybody help me? 

[v-yangliu-msft]

Hi  @Snip  ,

According to your description, create data from January 1, 2020 to April 1, 2020:

Here are the steps you can follow：

1. Create calculate table.

week of year = WEEKNUM('Table'[Date],1)

2. Create measure.

first_week =
var _1=SELECTEDVALUE('Date'[Date])
var _once=CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),WEEKNUM(_1)='Table'[week of year]))
return IF(MAX('Table'[week of year])<>WEEKNUM(_1),BLANK(),_once)

_two_twifth_ ratio for eacg =
var _1=SELECTEDVALUE('Date'[Date])
var _two_twelfth=CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[week of year]>=WEEKNUM(_1)+1&&'Table'[week of year]<=WEEKNUM(_1)+11))
var _total = CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))))
return
IF(MAX('Table'[week of year])=WEEKNUM(_1),BLANK(),DIVIDE(_two_twelfth,_total))

3. Result

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

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

[amitchandak]

@Snip , refer if my week on week blog, that can help you

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

Also, refer this method, in case you need trend -https://www.youtube.com/watch?v=duMSovyosXE