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

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 ) ) )
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Now it's blank...I am trying to tinker with it myself too by the way....

     

     

    • TheoC's avatar
      TheoC
      Community Champion
      4 years ago

      Anonymous try the below:

       

      14 Days = 

      VAR_1 = LASTDATE ( 'Terms & Hires SQL'[Fiscal Week Ends] ) - 7
      VAR_2 = _1 - 14 
      RETURN
      CALCULATE ( SUM ('Terms & Hires SQL'[Net Hires] ) , FILTER ( ALL ( 'Terms & Hires SQL'[Fiscal Week Ends]) , AND ( 'Terms & Hires SQL'[Fiscal Week Ends] > _2 , 'Terms & Hires SQL'[Fiscal Week Ends] <= _1 ) ) )

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        It was giving me errors so I modified it (bottom snip)