Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Selected Week from Next 4 week value calculation .

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 .

  • tamerj1's avatar
    tamerj1
    3 years ago

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

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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

    • tamerj1's avatar
      tamerj1
      Community Champion
      3 years ago

      Anonymous 
      Just Change SUM to AVERAGE

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        tamerj1  I have tired but avg not matching sir 

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    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 )
)

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

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

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        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 )
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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