Can we calculate Today's week number - number from Text.Middle M code ?

[Anonymous]

Hi,

I'm getting week number with M code(Text.Middle([Source Name],1,2)) from 'Source Name' column which will be 11.

If today's week number is 35 then hope to calculate Today's week number(variable) - 'Source Name' week number which will be 24.

I don't need all period of data source so that I can filter the data source only in the past 26 weeks(half year) with this calculations using filter 'Less than 26'.

[rajendraongole1 · Accepted solution]

Hi @Anonymous calculate the current week number as below

use the add column >> custom column in query editor, 

Date.WeekOfYear(DateTime.LocalNow())

output you will get 35 current weeknumber

create one more new column to find the difference 

Now, Filters the data to include only records from the past 26 weeks

Hope this works

[rajendraongole1 · Accepted solution]

Hi @Anonymous calculate the current week number as below

use the add column >> custom column in query editor, 

Date.WeekOfYear(DateTime.LocalNow())

output you will get 35 current weeknumber

create one more new column to find the difference 

Now, Filters the data to include only records from the past 26 weeks

Hope this works

[Anonymous]

Hi 

Thanks for your support.

Can we combine them in 1 M code at 1 column?

e.g. Week Difference = Date.WeekOfYear(DateTime.LocalNow()) - Text.Middle([Source Name],1,2) 

without creating seperate custom columns?

[rajendraongole1 · Accepted solution]

Hi @Anonymous -yes , we can combine the calculation into a single custom column without creating separate steps

AddWeekDifference = Table.AddColumn(
Source,
"Week Difference",
each Date.WeekOfYear(DateTime.LocalNow()) - Number.FromText(Text.Middle([Source Name], 2, 2)),
Int64.Type

Hope this helps.

[Anonymous]

Can you see what was wrong from my end? 

[Anonymous]

resolved thanks