Last n weeks and YTD

[ns292022]

Hello Freinds,

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

Name	PY	CY
Last week	500	600
Last 4 weeks	1000	2000
Last 12 weeks	4000	3000
FY22 YTD	10000	12000

Can someone please help?

Thanks,

Nitu

[Anonymous]

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]

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...