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.

edhans's avatar
edhans
Community Champion
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.