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

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    My calculated column DAX is rusty, but here's a basic measure that'll do the job

     

    LAST2WEEKSm =
    var currentweek = max(Table1[Fiscal Week Number])
    var previousweek = currentweek-1

    var currentweeknethires =
    CALCULATE(
    SUM(
    Table1[Net Hires]),
    Table1[Fiscal Week Number]=currentweek
    )

    var previousweeknethires =
    CALCULATE(
    SUM(
    Table1[Net Hires]),
    Table1[Fiscal Week Number]=previousweek
    )

    return
    currentweeknethires+previousweeknethires



    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Close? Because fiscal weeks differ from actual week numbers, for example, last week was Fiscal Week # 52 of 2021 and this week is Fiscal Week # 1 of 2022.

      So there will be a break in count, even though the dates on which fiscal weeks are consecutive... 

      For example: 

      Fiscal Week 50 of 2021

      Fiscal Week 51 of 2021

      Fiscal Week 52 of 2021 

      Fiscal Week 1 of 2022

       

      I don't think that week number is a good value to use, I changed it Fiscal Week End Date in hopes that it would just work but it did not.