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()

HarrisMalik's avatar
HarrisMalik
Icon for Continued Contributor rankContinued Contributor
10 years ago

what konstantinos means by option 2 is to import only those dates which are relevant while reading the date table from source. You can also try another option, put the date in the page level or report level filters and filter out only the periods you need but the downside is that you have to change the filter everytime you want to see the new dates.

 

The best option is to only read the dates which are relevant during ETL.

 

Regards

Harris

  • 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.