Hi and good day can anyone help me on my calculated column, on my table given Location, Wk no, WKly Plan and Trade. What I required is1. Max Plan = the maximun number we plan per Location on a certain wk. on my sample North Wk 32 = 45 and Wk 33 = 26. Then the Max. Plan Column will registered 452. Pla/Location = is total of what we plan from Wk 01 to previous WK. 3. Plan/Location/Wk = is the sum of plan per location per week, on my sample North WK32 = 45, WK33=264. Plan/Location/Wk/Trade = is the sum of plan per location per week per Trade, on my sample North,WK32, Mech = 23, E&I=7 and Scaff=15 NOTE: Calculation start from WK 01 to lessthan from the current week.DESIRED OUTPUTLocationWeek no: WKLY_PlanTradeMax. Plan/LocationPlan/LocationPlan/Location/WkPlan/Location/Wk/TradeSouth324Mech27482712South332Mech2748218North322Mech45714523North333Mech45712610North325Mech45714523North331Mech45712610North325Mech45714523North332Mech45712610North325Mech45714523North331Mech45712610North321Mech45714523North332Mech45712610North325Mech45714523North331Mech45712610South322Mech27482712South331Mech2748218South321Mech27482712South332Mech2748218South321Mech27482712South331Mech2748218South322Mech27482712South331Mech2748218South322Mech27482712South331Mech2748218North325Scaff45714515North332Scaff45712612South325Scaff27482711South333Scaff27482111South321Scaff27482711South331Scaff27482111North325Scaff45714515North331Scaff45712612North323Scaff45714515North333Scaff45712612South321Scaff27482711South331Scaff27482111South323Scaff27482711South333Scaff27482111North321Scaff45714515North333Scaff45712612North321Scaff45714515North333Scaff45712612South321Scaff27482711South333Scaff27482111South321E&I2748274South331E&I2748212North323E&I4571457North331E&I4571264South323E&I2748274South331E&I2748212North323E&I4571457North333E&I4571264North321E&I4571457

Hi AllanBerces please see the solution in attached PBIX. Please accept as solution if works. point no. 2 may be incorrect. requesting to share some more detail on that. Thanks

AllanBerces

Post Prodigy

1 year ago

Solved

Max, Sum per WK per Location

Hi and good day can anyone help me on my calculated column, on my table given Location, Wk no, WKly Plan and Trade. What I required is

1. Max Plan = the maximun number we plan per Location on a certain wk. on my sample North Wk 32 = 45 and Wk 33 = 26. Then the Max. Plan Column will registered 45

2. Pla/Location = is total of what we plan from Wk 01 to previous WK.

3. Plan/Location/Wk = is the sum of plan per location per week, on my sample North WK32 = 45, WK33=26

4. Plan/Location/Wk/Trade = is the sum of plan per location per week per Trade, on my sample North,WK32, Mech = 23, E&I=7 and Scaff=15

NOTE: Calculation start from WK 01 to lessthan from the current week.

DESIRED OUTPUT

Location	Week no:	WKLY_Plan	Trade	Max. Plan/Location	Plan/Location	Plan/Location/Wk	Plan/Location/Wk/Trade
South	32	4	Mech	27	48	27	12
South	33	2	Mech	27	48	21	8
North	32	2	Mech	45	71	45	23
North	33	3	Mech	45	71	26	10
North	32	5	Mech	45	71	45	23
North	33	1	Mech	45	71	26	10
North	32	5	Mech	45	71	45	23
North	33	2	Mech	45	71	26	10
North	32	5	Mech	45	71	45	23
North	33	1	Mech	45	71	26	10
North	32	1	Mech	45	71	45	23
North	33	2	Mech	45	71	26	10
North	32	5	Mech	45	71	45	23
North	33	1	Mech	45	71	26	10
South	32	2	Mech	27	48	27	12
South	33	1	Mech	27	48	21	8
South	32	1	Mech	27	48	27	12
South	33	2	Mech	27	48	21	8
South	32	1	Mech	27	48	27	12
South	33	1	Mech	27	48	21	8
South	32	2	Mech	27	48	27	12
South	33	1	Mech	27	48	21	8
South	32	2	Mech	27	48	27	12
South	33	1	Mech	27	48	21	8
North	32	5	Scaff	45	71	45	15
North	33	2	Scaff	45	71	26	12
South	32	5	Scaff	27	48	27	11
South	33	3	Scaff	27	48	21	11
South	32	1	Scaff	27	48	27	11
South	33	1	Scaff	27	48	21	11
North	32	5	Scaff	45	71	45	15
North	33	1	Scaff	45	71	26	12
North	32	3	Scaff	45	71	45	15
North	33	3	Scaff	45	71	26	12
South	32	1	Scaff	27	48	27	11
South	33	1	Scaff	27	48	21	11
South	32	3	Scaff	27	48	27	11
South	33	3	Scaff	27	48	21	11
North	32	1	Scaff	45	71	45	15
North	33	3	Scaff	45	71	26	12
North	32	1	Scaff	45	71	45	15
North	33	3	Scaff	45	71	26	12
South	32	1	Scaff	27	48	27	11
South	33	3	Scaff	27	48	21	11
South	32	1	E&I	27	48	27	4
South	33	1	E&I	27	48	21	2
North	32	3	E&I	45	71	45	7
North	33	1	E&I	45	71	26	4
South	32	3	E&I	27	48	27	4
South	33	1	E&I	27	48	21	2
North	32	3	E&I	45	71	45	7
North	33	3	E&I	45	71	26	4
North	32	1	E&I	45	71	45	7

Need Help

Rupak_bi
1 year ago
Hi AllanBerces

please see the solution in attached PBIX.

Please accept as solution if works. point no. 2 may be incorrect. requesting to share some more detail on that.

Thanks
Max_Sum per week.pbix23 KB

4 Replies

Khashayar
Resolver I
1 year ago
Hi Allan
Here’s a clean DAX setup you can drop into Power BI. I’ll assume your fact table is Plans with columns:
Location (e.g., North)
Wk no (numeric, 1–53)
WKly Plan (number)
Trade (e.g., Mech, E&I, Scaff)

All calculations exclude future weeks and consider weeks from 01 up to (but less than) the current week as you requested.
Base measure
Total Plan := SUM(Plans[WKly Plan])

Current week helper
(Change the week-number logic if your week system differs.)
Current Week No :=
WEEKNUM ( TODAY() )    -- or WEEKNUM(TODAY(),2) for ISO-style starting Monday

Plan/Location/Wk
Sum of plan per Location per Week (independent of Trade).
Plan/Location/Wk :=
CALCULATE(
    [Total Plan],
    ALLEXCEPT(Plans, Plans[Location], Plans[Wk no]),
    FILTER(ALL(Plans[Wk no]), Plans[Wk no] < [Current Week No]) -- exclude future
)

Plan/Location/Wk/Trade
Sum of plan per Location per Week per Trade.
Plan/Location/Wk/Trade :=
CALCULATE(
    [Total Plan],
    ALLEXCEPT(Plans, Plans[Location], Plans[Wk no], Plans[Trade]),
    FILTER(ALL(Plans[Wk no]), Plans[Wk no] < [Current Week No]) -- exclude future
)

Pla/Location (cumulative to previous week)
Total planned from Wk 01 up to the previous week, by Location.
Pla/Location :=
CALCULATE(
    [Total Plan],
    ALLEXCEPT(Plans, Plans[Location]),
    FILTER(
        ALL(Plans[Wk no]),
        Plans[Wk no] >= 1 && Plans[Wk no] < [Current Week No]
    )
)

Max Plan (max weekly plan per Location up to current week)
Maximum of Plan/Location/Wk across weeks (≤ current week) for each Location.
Max Plan :=
VAR CW = [Current Week No]
VAR WeeklyByLoc :=
    SUMMARIZE(
        FILTER(ALL(Plans), Plans[Wk no] <= CW),
        Plans[Location], Plans[Wk no],
        "SumPlan", CALCULATE([Total Plan], ALLEXCEPT(Plans, Plans[Location], Plans[Wk no]))
    )
RETURN
CALCULATE(
    MAXX(WeeklyByLoc, [SumPlan]),
    ALLEXCEPT(Plans, Plans[Location])
)

How to use
Put these measures in a table/Matrix visual.
For Plan/Location/Wk, add Location and Wk no as fields.
For Plan/Location/Wk/Trade, add Location, Wk no, and Trade.
Pla/Location and Max Plan work at the Location granularity (you can show them alongside Location).

Tips / gotchas
Ensure Wk no is numeric (1–53). If it’s text like “WK 32”, create a numeric column.
If your “current week” should be a manual parameter (e.g., reporting cut-off), create a disconnected table with week numbers and use a SELECTEDVALUE measure instead of WEEKNUM(TODAY()).

If you found this post helpful, please consider accepting it as the solution so that other members can find it more easily.

Regards,
Khashayar Yazdani | Microsoft MCT
https://www.linkedin.com/in/khashayary/
- AllanBerces
  Post Prodigy
  1 year ago
  Hi Khashayar thank you very much for the reply, but when i used on my table all the measure looks no correct output accordimg to my preference.
  - Rupak_bi
    Super User
    1 year ago
    Hi AllanBerces
    
    please see the solution in attached PBIX.
    
    Please accept as solution if works. point no. 2 may be incorrect. requesting to share some more detail on that.
    
    Thanks
    Max_Sum per week.pbix23 KB

Forum Discussion

Max, Sum per WK per Location

4 Replies

Related content

Running Sum Max Date

Calculate Sum MAX

Sum of MAX

sum of max values

Count of Locations with Sum of Values between min-max values