Sum per Week

[AllanBerces]

Hi good day,

I need some help on my calculated column. I want the sum of forecast of each week for both area.

Week1 = North forecast + SouthEast forecast

Week2 = North forecast + SouthEast forecast

Desired Output

Thank you

[mdaatifraza5556]

HIi @AllanBerces Please try the below dax to get your result

Overall_Forecast =

    SUMX(

        FILTER(

            Region,

            Region[Week No] = EARLIER(Region[Week No])

        ),

        Region[Forecast]

    )

[MFelix]

Hi @AllanBerces ,

You can do this on two different ways in Power Query and in DAX.

Power Query:

• Do a group by week
• Select a sum of the forecast and all the rows

• Expand the columns Area and forecast from the new column created.

DAX:

• Add the following code to your table:

OverallForecastDAX = 
        	CALCULATE(
	        	SUM('Table'[Forecast]),
		        'Table'[Week] = EARLIEST('Table'[Week]),
		        REMOVEFILTERS(
			        'Table'[Area],
			        'Table'[Forecast]
		            )
	        )

Please see attach PBIX file.

[Bibiano_Geraldo · Accepted solution]

Hi @AllanBerces ,

You can achieve the desired output by creating a new calculated column with this DAX:

OverallForecast =
VAR CurrentWeek = 'Table'[Week No.]
RETURN
CALCULATE(
    SUM('Table'[Forecast]),
    ALLEXCEPT('Table', 'Table'[Week No.])
)

Your output will look like this:
 

[AllanBerces]

Hi @Bibiano_Geraldo @MFelix Thank you very much for the reply, working perfectly