Forum Discussion

qwaiti27231's avatar
qwaiti27231
Helper III
8 years ago
Solved

week on week

i need the dax formula for week on week

 

for eg 

 

count of order Id with 10/9/2017- count of order with 3/9/2017)/count Of order in 3/9/2017

For eg

 count of order at 3/9/2017= 32 count of order in 10/9/2017 =52

 

((52-32)/32)*100=62.50%

 

 

Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    8 years ago

    qwaiti27231

     

    You can create a measure like below:

     

    7 days ago value = CALCULATE(SUM(Table5[Value]),FILTER(ALL(Table5),Table5[Date]=MAX(Table5[Date])-7))

     

     

    Regards,

8 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    8 years ago

    qwaiti27231

     

    In this scenario, you must have a Year column and a Week Number column in your table. To compare the current week and previous week. You can create a measure like below:

     

     

    =
VAR CurrentWeekCountID =
    CALCULATE (
        COUNTA ( Table[ID] ),
        ALLEXCEPT ( Table, Table[Year], Table[WeekNumber] )
    )
VAR PreviousWeekCountID =
    CALCULATE (
        COUNTA ( Table[ID] ),
        FILTER (
            ALL ( Table ),
            Table[Year] = MAX ( Table[Year] )
                && Table[WeekNumber]
                    = MAX ( Table[WeekNumber] ) - 1
        ),
        ALLEXCEPT ( Table, Table[Year], Table[WeekNumber] )
    )
RETURN
    ( CurrentWeekCountID - PreviousWeekCountID )
        / PreviousWeekCountID

    If you still have any question, please share some sample data.

     

    Regards,