Calculate Measure value for current week and prior week

[KHK]

Hi ,

I have Period Name like Jan-24, using this column i want to calculate the measure value for current week and prior week.

Please let me know how to achieve?

[RADSr]

To get a practical answer you'll need to provide some more information, but conceptually there are a few ways to get relative time measures.   

One I've found effective is using an input date ( could be "today," or a prompted session variable, or another calculation ) to look up the period in the calendar and use that in a CASE statement  ( more accurately w/in the case function in Incorta ) 

case(lookup(<period>, $current_date ) = 'Jan-24', <measure>, 0 ) 

You'd need to confirm business rules e.g. fiscal v Gregorian calendar, does current date mean *today* today, or yesterday because we loaded data overnight and yesterday is the most current, etc.  

HTH

[RADSr]

Here's a formula I did w/ the online store data - it's business logic is incomplete as it doesn't account for the year, but it'll give you a starting point.   

The insight shows line total dollar amounts for January and zeros for everything else. 

BIG NOTE:  The time_dimension in the onlinestore schema defines the "id" field as its key.   In order for this formula to work you need to set "id" to dimension and "db_date" as the key.    The formula **will not error** if you don't, but will produce either nulls or the word #ERROR in your insight.    

====================================

case(

    lookup(

        OnlineStore.time_dimension.month_name, 

        OnlineStore.time_dimension.db_date, 

        $SV_OrderDate, 

        'blah'

    ) = OnlineStore.time_dimension.month_name, 

    OnlineStore.salesorderdetail.LineTotal, 

    0

)