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

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

The slicers are probably from the Date table so they always list all the values that exists in Date table. There is nothing to do with measures. 

You can create a 1) new Date table with caclulated tables and CALENDAR() function ( then add calculated columns) so it shows only the sales first and last dates dynamically or  2) you can filter the date table in the query when you load the Date table.

 

 

  • 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