Forum Discussion

CraigBlackman's avatar
CraigBlackman
Icon for Helper III rankHelper III
10 years ago
Solved

Future dates displayed and should not be.

I have a simple table that list the date, net sales an previosu years sales  using the following formula   P/Y Sales = SUMX(DimDate,CALCULATE([Net Sales],DATEADD(DimDate[Date],-1,YEAR))).   Probl...
Need Help
Show and Tell
  • CraigBlackman's avatar
    CraigBlackman
    10 years ago

    This is what I have

     

    = Table.SelectRows(#"Renamed Columns1", each [Date] <= DateTime.LocalNow()

CraigBlackman's avatar
CraigBlackman
Icon for Helper III rankHelper III
10 years ago

Sorry with all the questions.

 

The order_header table only pulls through order taken up to the present day, so there will never be orders on our system beyond the current days date.

 

The DimDate table is a static table up to end of 2020.

 

What I am unsure of is how to only import relevant dates which are relevant while reading the date table from source.

 

Thanks Craig

konstantinos's avatar
konstantinos
Icon for Memorable Member rankMemorable Member
10 years ago

First I suppose that you use PowerBI Desktop or Excel. Go to the PowerQuery that import the Date table  and then filter the Date column to be before or equal to a random date.

 

 

 

Then Open the advanced editor and change the date ( after the <= )  with the function 

DateTime.LocalNow()

 The format of date column must me Date/Time

 

 

 

Every time you refresh it should filter the dates till today 

 

Hope that helps

  • CraigBlackman's avatar
    CraigBlackman
    Icon for Helper III rankHelper III
    10 years ago

    Ok, so as suggested i get this error.

     

    Expression.SyntaxError: Token RightParen expected.

     

  • konstantinos's avatar
    konstantinos
    Icon for Memorable Member rankMemorable Member
    10 years ago

    You probably missed a parenthesis at the end

  • CraigBlackman's avatar
    CraigBlackman
    Icon for Helper III rankHelper III
    10 years ago

    This is what I have

     

    = Table.SelectRows(#"Renamed Columns1", each [Date] <= DateTime.LocalNow()

  • CraigBlackman's avatar
    CraigBlackman
    Icon for Helper III rankHelper III
    10 years ago

    It's obviosu when you take a step back. Missed the extra ), so we are all now working as i'd like!

     

    konstantinos thanks for the solution.

     

    Thanks everyone else too!