Forum Discussion
Helper IIIFuture 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))).
Problem is the tabel is now running dates to 2020 and i am only intertested in giving my team the ability to show up to and inclduing the current date, Future dates beyond that not required.
How do i get around this?
Thanks
Craig
This is what I have
= Table.SelectRows(#"Renamed Columns1", each [Date] <= DateTime.LocalNow()
15 Replies
- elliotdixon
Responsive Resident
HI CraigBlackman In my dates table I have a column
IsInCurrentYear = if(YEAR(NOW())= [Year],1,0)
You could add this to your dates table and then filter on it - something like
P/Y Sales = SUMX(DimDate,CALCULATE([Net Sales],DATEADD(DimDate[Date],-1,YEAR)),filter(DimDate,DimDate[IsInCurrentYear]=1))
ED
HarrisMalikContinued Contributor
Hi
Do you have data in your fact table for future dates? e.g. Net Sales for some dates in 2018?
If answer is no then you can use following:
PY Sales = CALCULATE(SUM(NetSales[NetSales]), SAMEPERIODLASTYEAR(DimDate[Date]))
If you have sales data for future periods and you do not want to show PY Sales for those periods you can use:
PY Sales = CALCULATE(SUM(NetSales[NetSales]), SAMEPERIODLASTYEAR(DimDate[Date]),DimDate[Date]<= TODAY())
I hope it helps.
Note: Important thing is how you modelled the relationships.
Regards
Harris
Regards
Harris
- konstantinos
Memorable Member
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 ) ) )
)
CraigBlackmanThanks 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
