Adding a previous Week column in a calendar

[jnunn]

I need some help adding a Previous Week as a column in a calendar.    

I am trying to add a column that flags which week is last week bassed on todays date.  I tried to use the "Date.IsInPreviousWeek" function (formula below) and what I get is True for every row in my table.    

"AddPreviuosWeek = Table.AddColumn(AddCalendarYear, "Previous Week", each Date.IsInPreviousWeek(Date.AddDays(DateTime.FixedLocalNow(), -7)), type text)"

What am I doing wrong?

Is there a better function to use to find which dates are in the previous week?

Thanks

Jeff

[edhans]

AddPreviuosWeek = Table.AddColumn(AddCalendarYear, 
"Previous Week", 
each Date.IsInPreviousWeek(Date.AddDays(DateTime.FixedLocalNow(), -7)), type text)

You are subtracting 7 days from today every time - DateTime.FixedLocalNow() is equivalent to TODAY() in Excel. You want to replace that with the Date column in your calendar table.

And it should return a TRUE or FALSE as "type logical" not "type text"

Try this, assuming your date column is called Date (case sensitive):

AddPreviuosWeek = Table.AddColumn(AddCalendarYear, 
"Previous Week", 
each Date.IsInPreviousWeek(Date.AddDays([Date], -7)), type logical)

[jnunn]

Thank you. 

This gave me the expected results.  Now there is another issue.  When I apply my changes and leave the editor my new column is showing as all blanks.  I see the T/F values in the editor but not in the DAX side of power BI.  Is there something with this formula that would not allow it to come over.  

Please advise

J

[edhans · Accepted solution]

Did you confirm it is type logical? You should see a red x/green checkmark for the data type. It should show up just fine in your tables on the DAX/Visualization side, and you use it in expressions by comparing it to TRUE or FALSE

please mark the answer as a solution if everything is good. Post back with additional questions.