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

This will return PY sales if you have Net sales else will be blank 

 PY Sales=
IF (
NOT ( ISBLANK ( [Net Sales] ) ),
SUMX ( DimDate, CALCULATE ( [Net Sales], DATEADD ( DimDate[Date], -1, YEAR ) ) )
)

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

Thanks for that. I have updated the PY sales and PY orders field.

 

The trouble is the year, month and day slicers still show years months and days going all the way to 2020

 

Craig

 

 

 

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

    CraigBlackman Can you share a screenshot with us? and secondly what type of relationship you have between your date dimension and fact?

     

    Cheers

    Harris

  • 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