Forum Discussion

electrobrit's avatar
electrobrit
Post Patron
7 years ago
Solved

Week over Week HELP!

I am doing a report on something I thought would be so simple but it's not working. 
I have to show Cases opened in the week, compared to last week (there are filters they will have by team or indiv or other).
So I need a fairly simple calc. The way I am calculating Total Number of Cases is by a measure:

Total Number of Cases =
CALCULATE(
Count(VW_Cases[CaseCreatedOn])
)

There are quite a few Dax formulas that offer to return previous week I have found that will not let me use that measure in so wondering if it should be a calc column.
(I'm questioning everything)

1. Can anyone provide this simple formula for me and tell me what I am doing wrong? I want a simple line chart showing this week and last week.

2. Also, what is the formula for % week var over last week for a card? like change in how many cases created (%)

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi electrobrit

     

    You may add a week of year column first in Query Editor. Then you may refer to below measure to get the table as requested.

    LastWeekTotal =
CALCULATE (
    COUNT ( VW_Cases[CaseCreatedOn] ),
    FILTER (
        ALL ( VW_Cases ),
        VW_Cases[Week of Year]
            = MAX ( VW_Cases[Week of Year] ) - 1
    )
)
    WeekOverWeek =
VAR thisweek =
    CALCULATE (
        COUNT ( VW_Cases[CaseCreatedOn] ),
        FILTER (
            ALL ( VW_Cases ),
            VW_Cases[Week of Year] = MAX ( VW_Cases[Week of Year] )
        )
    )
VAR lastweek =
    CALCULATE (
        COUNT ( VW_Cases[CaseCreatedOn] ),
        FILTER (
            ALL ( VW_Cases ),
            VW_Cases[Week of Year]
                = MAX ( VW_Cases[Week of Year] ) - 1
        )
    )
RETURN
    DIVIDE ( thisweek - lastweek, lastweek )

     

    Regards,

    Cherie

2 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    Hi electrobrit

     

    You may add a week of year column first in Query Editor. Then you may refer to below measure to get the table as requested.

    LastWeekTotal =
CALCULATE (
    COUNT ( VW_Cases[CaseCreatedOn] ),
    FILTER (
        ALL ( VW_Cases ),
        VW_Cases[Week of Year]
            = MAX ( VW_Cases[Week of Year] ) - 1
    )
)
    WeekOverWeek =
VAR thisweek =
    CALCULATE (
        COUNT ( VW_Cases[CaseCreatedOn] ),
        FILTER (
            ALL ( VW_Cases ),
            VW_Cases[Week of Year] = MAX ( VW_Cases[Week of Year] )
        )
    )
VAR lastweek =
    CALCULATE (
        COUNT ( VW_Cases[CaseCreatedOn] ),
        FILTER (
            ALL ( VW_Cases ),
            VW_Cases[Week of Year]
                = MAX ( VW_Cases[Week of Year] ) - 1
        )
    )
RETURN
    DIVIDE ( thisweek - lastweek, lastweek )

     

    Regards,

    Cherie

    • electrobrit's avatar
      electrobrit
      Post Patron
      7 years ago

      i like this for WTD but is there a way to see it by day as well? I would like to do a line chart showing daily this week compared to last week same day