Forum Discussion
Needs to have same dates in Previous Year Using DAX
I have some Random Dates From Current Year and I want to have same Dates in Previous Year. I tried below DAX to do that but i am not able to access table Varibale below.
First I get all dates in a Tables Variable where Flag<>-1 in Sales Fact Table. Then I will go back one Year and will compare same monthDay in table Variable with previous year.
Sales.LY :=
VAR tbl=CALCULATETABLE(ALL('Date'),Sales[Flag]<>-1)
RETURN
CALCULATE (
[Sales Amount],
FILTER (
ALL ( 'Date' ),
'Date'[Year] = VALUES ( 'Date'[YEAR] ) - 1
&& CONTAINS(VALUES('tbl'[DAYMONTHNO]),
'tbl'[DAYMONTHNO],
'tbl[DAYMONTHNO])
)
)How can I access Values on Table Variable as in above DAX?
Hi Anonymous,
Try the demo in the attachment, please. You need a date table in your scenario.
Measure = IF ( MIN ( Fact_Sales[Offer Flag] ) = -1, BLANK (), CALCULATE ( SUM ( Fact_Sales[Sales_Amount] ), SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) ) )Best Regards,
Dale
15 Replies
Use DATEADD function witht the YEAR.
Sales.LY = CALCULATE([Sales], DATEADD(Subscribers[Sale Date].[Date], -1, YEAR))
I meant use the DATEADD in your RETURN
LY = VAR tbl=CALCULATETABLE(ALL(Sales[Date].[Date]),Sales[Flag]<>-1) RETURN CALCULATE([Sales], DATEADD(Sales[Date].[Date], -1, YEAR))
Thanks Anuradha.
This does not work for me. Its bring me data for whole month of Previous Year. I have Sales Offer for 10 Days of September of this year and I want to see Sales of Last year for only 10 Days. IF I write a sample Query in SQL It will look like. I want to Translate it to DAX.(Suppose Current Year 2018)
Select Sum(Sales) From FactSale s join Dimdate d on S.DateKey=d.DateKey Where year=2017 AND Month='Sep' AND D.Date in (Select DateAdd(D.Date,-1,Year) From FactSale s join Dimdate d on S.DateKey=d.DateKey Where year=2018 and Month='Sep' and Flag<>-1)
Share some sample data/pbix
