cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ns292022
New Member

Last n weeks and YTD

Hello Freinds,

 

I have a table with PY, CY and weekstart column, and I want to create a table to show the following data:

 

NamePYCY
Last week500600
Last 4 weeks10002000
Last 12 weeks40003000
FY22 YTD1000012000

 

Can someone please help?

 

Thanks,

Nitu

2 REPLIES 2
Ailsa-msft
Community Support
Community Support

Hi @ns292022 

Can you provide your original data ? From the data you have provided so far, I have no way to give the calculation logic directly. It's easier for you to provide the raw data and the results you want.

 

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

daXtreme
Super User
Super User

You have to have a date table with weeks which are numbered within each year with the same scheme (1,2,3...,52/53). Then it'll be easy to move forward and backward in such a calendar.

 

// Say you have such a calendar.
// Say, your base measure is [Base].
// Also, say that the calendar also
// has a column with False for each
// day apart from today which will
// have True assigned. Name the
// hidden column Dates[IsToday].
// You should also have 2 other columns.
// Dates[YearWeekNumber] will be
// the column that numbers the weeks
// within a year, always restarting at
// the beginning of next year. The other
// will be Dates[AbsoluteWeekNumber]
// and will hold numbers from 1 to the
// number of all weeks in the calendar
// consecutively. This is to be able
// to move easily through weeks.
// Bear in mind, please, that no matter
// what units of time you use in your
// model, be it weeks only, months only,
// years only, the Dates table must always
// be a proper date table with all the days
// covering all the years. Some columns in
// such a table can be hidden (since they
// don't apply or are not meant to be used
// by the end user), but they must be present
// for everything to work correctly, especially
// the time-intel functions.
//
// Then:

[Last Week] =
var CurrentWeek_AbsoluteWeekNumber =
    calculate(
        selectedvalue( Dates[AbsoluteWeekNumber] ),
        Dates[IsToday], // this is a logical column
        all( Dates )
    )
var LastWeekValue = 
    calculate(
        [Base],
        Dates[AbsoluteWeekNumber] = CurrentWeek_AbsoluteWeekNumber - 1,
        all( Dates )
    )
return
    LastWeekValue
    
    
[Last Week PY] =
var CurrentWeek_AbsoluteWeekNumber =
    calculate(
        selectedvalue( Dates[AbsoluteWeekNumber] ),
        Dates[IsToday], // this is a logical column
        all( Dates )
    )
var PrevWeek_AbsoluteWeekNumber = CurrentWeek_AbsoluteWeekNumber - 1
var PrevWeek_YearNumber =
    calculate(
        selectedvalue( Dates[YearNumber] ),
        Dates[AbsuluteWeekNumber] = PrevWeek_AbsoluteWeekNumber,
        all( Dates )
    )
var PrevWeek_YearWeekNumber =
    calculate(
        selectedvalue( Dates[YearWeekNumber] ),
        Dates[AbsoluteWeekNumber] = PrevWeek_AbsoluteWeekNumber,
        all( Dates )        
    )
var PrevWeek_PYValue =
    calculate(
        [Base],
        treatas(
            {PrevWeek_YearNumber - 1, PrevWeek_YearWeekNumber},
            Dates[YearNumber],
            Dates[YearWeekNumber]
        ),
        all( Dates )
    )
return
    PrevWeek_PYValue
    
// the other measures can be constructed similarly...

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10

The Power BI Community Show

Join us on October 3 at 11 am PST when Amit Chandak, a Power BI Super User, will demo how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors