Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculating Previous Values

Hi,    I am posting the same question again in hopes of finding the answer. I need to calculate the value for the past two weeks from Net Hire column. The dax measure I have is not giving me any va...
General Comment
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    Would something like this serve? I am assuming that the source data has a [Fiscal Week Ends] column, not a hire date. 

    Last 2 Weeks New =


    var currentweek = [Fiscal Week Num]

    var previousweek = [Fiscal Week Num]-1
    var currentweekhires = 
    CALCULATE( 
                       SUM(
                                   'Terms & Hires SQL'[Net Hires]),
                                [Fiscal Week End]=currentweek

    var previousweekhires = 
    CALCULATE( 
                       SUM(
                                   'Terms & Hires SQL'[Net Hires]),
                                [Fiscal Week End]=previousweek

     

     

    return

    currentweekhires+previousweekhires

TheoC's avatar
TheoC
Community Champion
4 years ago

Anonymous Could you please provide the output you want from your measure as it may not be clear exactly what you are trying to achieve?  What is the output you want for the Last 2 Weeks New column in your image?

 

Thanks heaps,

Theo

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

A sum of net hires for the past two weeks and it should be rolling...

Starting at fiscal week 48

Fiscal week 49 = sum of net hires for week 48

Fiscal week 50 = sum of net hires for week 49 and 48

Fiscal week 51 = sum of net hires for week 49 and 50 

Fiscal week 52 = sum of net hires for week 50 and 51 

And so on


Fiscal week end date should be used as an identifier since it contains an actual date 

Dataset contains other columns that will be used as filters, location for example, so the values in the measure should change with the filters. 

  • TheoC's avatar
    TheoC
    Community Champion
    4 years ago

    Hi Anonymous,

     

    You can use the following and adapt it your tables / column names:

    14 Days = 

    VAR _1 = LASTDATE ( 'Table'[Date] ) // This is the date in your fact table
    VAR _2 = _1 - 14 // represents the 14 days in the respective fortnight
    RETURN
    CALCULATE ( SUM ('Table'[Amount] ) , FILTER ( ALL ( 'Date') , AND ( 'Date'[Date] > _2 , 'Date'[Date] <= _1 ) ) )

     

    The above assumes you have a Date / Calendar table.  If not, please adjust the 'Date' to the respective date column in your fact table.   

     

    Hope this helps 🙂

    Theo 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      this is giving me the same value as net hires....

       

       

      • TheoC's avatar
        TheoC
        Community Champion
        4 years ago

        Sorry mate, just change VAR _1 to the following: 

         

        VAR _1 = LASTDATE ( 'Table'[Date] ) - 7

         

        So the whole measure will be:

         

        14 Days = 

        VAR _1 = LASTDATE ( 'Table'[Date] ) - 7 // This is the date in your fact table
        VAR _2 = _1 - 14 // represents the 14 days in the respective fortnight
        RETURN
        CALCULATE ( SUM ('Table'[Amount] ) , FILTER ( ALL ( 'Date') , AND ( 'Date'[Date] > _2 , 'Date'[Date] <= _1 ) ) )