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

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

 

 

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.