Forum Discussion

jnunn's avatar
jnunn
Frequent Visitor
6 years ago
Solved

Adding a previous Week column in a calendar

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

Need Help
  • edhans's avatar
    edhans
    6 years ago

    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.

3 Replies

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

     

    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's avatar
      jnunn
      Frequent Visitor
      6 years ago

      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's avatar
        edhans
        Community Champion
        6 years ago

        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.