Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Current Week vs Prior Week (same year)

I would like to create a line chart showing showing my current week vs last week of a total value. I know I need 2 measures here, but the week time base equations I've tried are not working.  I have...
Need Help
  • edhans's avatar
    edhans
    6 years ago

    I've done some extensive work with weeks. What I use is the following logic:

    • Get the current week number for your data. Something like this:
    Prior Year Sales = 
VAR CurrentWeek =
    MAXX(
        'Sales',
        RELATED( 'Date'[Week of Year] )
    )
VAR CurrentYear =
    MAXX(
        'Sales',
        RELATED( 'Date'[Year] )
    )
VAR PriorYearSales =
    SUMX(
        FILTER(
            ALL( Sales ),
            RELATED( 'Date'[Year] ) = CurrentYear - 1
                && RELATED( 'Date'[Week of Year] ) = CurrentWeek
        ),
        Sales[SubTotal]
    )
RETURN
    PriorYearSales

    You an see the 2011 week sales 23-26 are showing in the prior year for 2012 for those same weeks.

     

    Here is a link to my PBIX file if you want to tinker with it.

v-alq-msft's avatar
v-alq-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi, Anonymous 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

 

You may create a calculated column and a measure as bleow.

Calculated column:
Weeknum = WEEKNUM('Table'[Date])

Measure:
LastWeekValue = 
var _lastweeknum = SELECTEDVALUE('Table'[Weeknum])-1
var _result = 
CALCULATE(
    SUM('Table'[Value]),
    'Table'[Weeknum] = _lastweeknum
)
return
_result

 

Result:

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.