Current week sum should sum of next week and Next to next week

[Anonymous]

Hi Team,

I have requirement where I am calculting total on Weekly basis. I dont have date key, its Year, Month and Week.

Below is my data and required result. 

Condition : if the we its last week of the year then it should calculate the sum of next year 1st & 2nd week. there are few other columns such as Product_Group and Group_Type, etc which will be using as Slicers.

Year	Month	Week	Sales	Result	Formula
2023	Jan	1	10	30	Week2+Week3
2023	Jan	2	20	70	Week3+Week4
2023	Jan	3	30	90	Week4+Week5
2023	Jan	4	40	110	Week5+Week6
2023	Feb	5	50	130	Week6+Week7
2023	Feb	6	60	150	Week7+Week8
2023	Feb	7	70	170	Week8+Week9
2023	Feb	8	80	190	Week9+Week10
2023	Mar	9	90	210	Week10+Week11
2023	Mar	10	100	230	Week11+Week12
2023	Mar	11	110	250	Week12+Week13
2023	Mar	12	120
2023	Mar	13	130

In Advance

[VilmarSch · Accepted solution]

Veja se ajuda

Result = 
CALCULATE (
    SUM ( Tabela[Sales ] ),
    WINDOW ( 1, REL, 2, REL, ALLSELECTED ( Tabela[Week], Tabela[Month] ) )
)

[Anonymous]

@VilmarSch,

Thanks for your response !

Here is my screenshot from the report. Every last week of the month is not adding to the next week from the next month instead it showing blank.

Example: 21st week is showing blank instead it should add week 22 and week 23.

Can you please help on this.

Thanks in Advance!

[VilmarSch]

Aparentemente faltou remover o filtro do mês. Tenta incluir o mês dentro da ALLSELECTED

[TomMartens]

Hey @Anonymous ,

I tend to create a data model instead of complex measures, because sooner or later it will be likely that we hit the Auto-exist wall in the dreaded one table solution (https://www.sqlbi.com/articles/understanding-dax-auto-exist/). For this reason I created an extra table using Power Query derived from the Year and Week colum of the table you provided, the next picture shows that table:

The query selects the columns Year and Week, makes sure the rows are unique creates a column like so Year * 100 + week, the table gets sorted by the new column and finally an Index column is created. The next lines show the M code of the query:

let
    Source = Table.SelectColumns(#"Table", {"Year" , "Week"}),
    #"Removed Duplicates" = Table.Distinct(Source),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "YearWeek_Num", each [Year] * 100 + [Week]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"YearWeek_Num", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"YearWeek_Num", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "YearWeek_Index", 1, 1, Int64.Type)
in
    #"Added Index"

I also create a new column in the table that holds your data: Year * 100 + Week.

I use the YearWeek_Num columns to create a relationship:

Having this in place, the DAX statement becomes "simple":

Sales projection = 
var currentYearNum = CALCULATE( MAX( 'Some Kind of Calendar'[YearWeek_Index] ) )
var week_1 = currentYearNum + 1
var week_2 = currentYearNum + 2
return
CALCULATE(
    SUM( 'Table'[Sales ] )
    , 'Some Kind of Calendar'[YearWeek_Index] in {week_1 , week_2 }
    , ALL( 'Some Kind of Calendar' )
)

The table:

Please be aware that the columns Year and Week are coming from the "dimension" table.

Hopefully, this provides what you are looking for, at least some ideas.

Regards,

Tom

[Anonymous]

@TomMartens ,

Thank for your help !

It would be great if you can add the pbix file.

I have followed all the steps as you have mentioned above but I dont see any values coming out as expected.

Attaching the screenshots for the same.

Please let me know if I am missing anything here.

Thanks !

[TomMartens]

Hey @Anonymous ,

find the pbix attached.

Tom

[grazitti_sapna]

Hi @Anonymous 

You can try using:-

Result =
VAR CurrentYear = MAX('YourTable'[Year])
VAR CurrentWeek = MAX('YourTable'[Week])
VAR NextYear = CurrentYear + 1
VAR NextYearWeek1 = CALCULATE(SUM('YourTable'[Sales]), 'YourTable'[Year] = NextYear, 'YourTable'[Week] = 1)
VAR NextYearWeek2 = CALCULATE(SUM('YourTable'[Sales]), 'YourTable'[Year] = NextYear, 'YourTable'[Week] = 2)
RETURN
IF(
CurrentWeek = MAXX(FILTER('YourTable', 'YourTable'[Year] = CurrentYear), 'YourTable'[Week]),
SUM('YourTable'[Sales]) + NextYearWeek1 + NextYearWeek2,
SUM('YourTable'[Sales])
)
Thank you. Hope this will help.
kudos will be appreciated

[Anonymous]

@grazitti_sapna ,

Thanks for your response!

The above DAX is not working as expected.

Below Image = Sum(Sales)

below Image

Dax = 

Nextweeks Sales =

   VAR CurrentYear = MAX('src_horizon v_horizon_aggregate'[YEAR])

   VAR CurrentWeek = MAX('src_horizon v_horizon_aggregate'[WEEK])

   VAR _NextYear = CurrentYear +1

   VAR NextYearWeek1 = CALCULATE(SUM('src_horizon v_horizon_aggregate'[SALES]),'src_horizon v_horizon_aggregate'[YEAR] = _NextYear,'src_horizon v_horizon_aggregate'[WEEK] = 1)

   VAR NextYearWeek2 = CALCULATE(SUM('src_horizon v_horizon_aggregate'[SALES]),'src_horizon v_horizon_aggregate'[YEAR] = _NextYear, 'src_horizon v_horizon_aggregate'[WEEK] = 2)

   Return

   IF(

       CurrentWeek = MAXX(FILTER('src_horizon v_horizon_aggregate','src_horizon v_horizon_aggregate'[YEAR] = CurrentYear),'src_horizon v_horizon_aggregate'[WEEK]),

       SUM('src_horizon v_horizon_aggregate'[SALES]) + NextYearWeek1 + NextYearWeek2,

       SUM('src_horizon v_horizon_aggregate'[SALES]))

========================

Both the values are same.

[TomMartens]

Hey @Anonymous ,

I think the formulas are not corresponding to your description ...
I understand the business rule as: take the sum of the next two weeks after the current week.

If the current week is week 3, the expected result will be 90 (40 + 50), week 4 + week 5, but your fomula states: week 6 + week 7.

Please adjust the description and result column or adapt the formula.

Thanks,

Tom

[Anonymous]

@TomMartens ,

Thanks ! I made the corrections, if you can help we with the DAX it would be great help.

Thanks!