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 values and I do not see where the mistake is, I've reformatted it multiple times but I cannot get it to show the values. Can someone please help? 
 

Last 2 Weeks new =

VAR Fiscal_Week_End = MAXA ('Terms & Hires SQL'[Fiscal Week Ends])
VAR Net_Hires = CALCULATE (SUM('Terms & Hires SQL'[Net Hires]),
 
FILTER (ALL ('Terms & Hires SQL'),
'Terms & Hires SQL'[Fiscal Week Ends] < Fiscal_Week_End))

 

Var cal = CALCULATE(SUM('Terms & Hires SQL'[Net Hires]),

 

FILTER (ALL('Terms & Hires SQL'[Net Hires]),
'Terms & Hires SQL'[Fiscal Week Ends] >= Fiscal_Week_End
&& 'Terms & Hires SQL'[Fiscal Week Ends] <= Fiscal_Week_End))

 

RETURN
cal
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

14 Replies

  • 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

    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

      That didn't work even when I found the columns.... 

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi, 

         

        I just realised I may be an idiot; you're after a calculated column, not a measure, right? In which case the following may not be relevant.

         

        The DAX wasn't exact. If your source data has the week they occurred in as a column name, you can use that directly. If not, and you have some sort of date table, you may need to use the week number that is reference from that table.

         

        You will need the table name e.g.

        'Terms & Hires SQL'[Fiscal Week Ends]=currentweek

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    4 years ago

    Hi Anonymous ,

     

    Message 11 works well. Please try to create a similar DAX formula.

    If you want to calculate with consecutive "week", you need to first create the year week column, then sort it with rankx function, and then use [sort] to represent the week based on the formula.

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.