topic Re: Selected Week from Next 4 week value calculation . in DAX Commands and Tips

Selected Week from Next 4 week value calculation .

Anonymous — Fri, 16 Dec 2022 04:38:20 GMT

I have table name called Invenoty sheet . this table look like . based on the wknum selection will show rolling average to next 4 weeks .

(Wknum is text format)

DATE ATTRIBUTE wknum value

15 March 2021 Demand wk01 45

22 March 2021 Demand wk02 55

29 March 2021 Demand wk03 65

05 April 2021 Demand wk04 45

12 April 2021 Demand wk05 95

19 April 2021 Demand wk06 25

26 April 2021 Demand wk07 35

I am Used below Measure

Mesaure = CALCULATE(sum(Append1[Value]),FILTER(Append1,Append1[Attributes]="Demand"),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),1,MONTH))

Expected Output :

wknum ATTRIBUTE value Selected week from Next 4 week

wk01 Demand 45 220 => 55+55+65+45

wk02 Demand 55 260 => 55+65+45+95

wk03 Demand 65 250 => 65+45+95+45

wk04 Demand 45 240 =>45+95+45+55

wk05 Demand 95 195 =>95+45+55

wk06 Demand 45 90 =>45+55

wk07 Demand 55 55 =>55

Looking for support .. thanks in advance .

Re: Selected Week from Next 4 week value calculation .

tamerj1 — Fri, 16 Dec 2022 05:23:43 GMT

Hi Anonymous
If [wknum] column is from the 'Date' table then it should work with a small modification

Mesaure = CALCULATE ( SUM ( Append1[Value] ), Append1[Attributes] = "Demand", DATESINPERIOD ( 'Date'[Date], MIN ( 'Date'[Date] ), 1, MONTH ), ALLSELECTED ( Append1 ) )

Re: Selected Week from Next 4 week value calculation .

Anonymous — Fri, 16 Dec 2022 06:56:00 GMT

tamerj1 Not a column from Date. it is a text column(weeknum) in the table.

Re: Selected Week from Next 4 week value calculation .

tamerj1 — Fri, 16 Dec 2022 07:36:28 GMT

Hi Anonymous
Please try

Mesaure = VAR CurrentDate = CALCULATE ( MIN ( 'Date'[Date] ), CROSSFILTER ( 'Date'[Date], Append1[Date], BOTH ) ) RETURN CALCULATE ( SUM ( Append1[Value] ), Append1[Attributes] = "Demand", DATESINPERIOD ( 'Date'[Date], CurrentDate, 1, MONTH ), ALL ( Append1 ) )

Re: Selected Week from Next 4 week value calculation .

Anonymous — Fri, 16 Dec 2022 09:06:49 GMT

tamerj1 i have tired above measure .. but i am getting CROSSFILTER function can only use the two column references participating in a relationship.

Re: Selected Week from Next 4 week value calculation .

tamerj1 — Fri, 16 Dec 2022 09:56:54 GMT

Anonymous
Which columns are invloved in the relationship? Is there even a relationship between the two tables?

Re: Selected Week from Next 4 week value calculation .

Anonymous — Fri, 16 Dec 2022 10:29:15 GMT

tamerj1 that issues has been fixed but sum values are showing different . I have attached Power bi file for your reference sir

Power bi File

Re: Selected Week from Next 4 week value calculation .

tamerj1 — Fri, 16 Dec 2022 11:47:58 GMT

Hi Anonymous
You already have a Date table, it is better to use it. Please refer to attached file with the proposed solution

Re: Selected Week from Next 4 week value calculation .

Anonymous — Fri, 16 Dec 2022 12:46:13 GMT

tamerj1 its working Now perfectly . I have one more question How to convert above measure SUM to average ?

Re: Selected Week from Next 4 week value calculation .

Anonymous — Fri, 16 Dec 2022 12:54:18 GMT

tamerj1 its working perfectly . one more questions how to convert above measure SUM to average ?

Re: Selected Week from Next 4 week value calculation .

tamerj1 — Fri, 16 Dec 2022 13:02:20 GMT

Anonymous
Just Change SUM to AVERAGE

Re: Selected Week from Next 4 week value calculation .

Anonymous — Fri, 16 Dec 2022 13:07:33 GMT

tamerj1 I have tired but avg not matching sir

Re: Selected Week from Next 4 week value calculation .

tamerj1 — Fri, 16 Dec 2022 13:31:01 GMT

Anonymous
Please check the updated sample file.