Calculating Previous Values

[Anonymous]

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

[V-lianl-msft]

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.

[Anonymous · Accepted solution]

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]

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

[Anonymous]

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]

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. 

[Anonymous]

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

[TheoC]

@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]

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]

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]

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

[TheoC]

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]

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

[TheoC]

@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]

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