Needs to have same dates in Previous Year Using DAX

[Anonymous]

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?

[Anuradha]

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

[Anonymous]

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) 

[Anuradha]

Share some sample data/pbix

[Anonymous]

This is sample Data I want to have Sales of Same Dates of Previous Year.

Date	                 Sales
2018-03-01 00:00:00.000	  309807.80142858
2018-03-02 00:00:00.000	  362737.72000000
2018-03-03 00:00:00.000	  441974.95476191
2018-03-04 00:00:00.000	  302750.95000000
2018-03-05 00:00:00.000	  286119.32333337
2018-03-06 00:00:00.000	  237905.76476192
2018-03-07 00:00:00.000	  306020.08809528
2018-03-08 00:00:00.000	  333923.96285720
2018-03-09 00:00:00.000	  353492.70238098
2018-03-10 00:00:00.000	  457035.59000000
2018-03-11 00:00:00.000	  242836.11904764
2018-03-12 00:00:00.000	  265318.37571430
2018-03-13 00:00:00.000	  295726.70000000
2018-03-14 00:00:00.000	  233334.22000006
2018-03-15 00:00:00.000	  277178.19000001
2018-03-16 00:00:00.000	  286685.79809526

[Anuradha]

Try this, this will give you exact value from the specifically on the previous day of the year.

LY_Sale = LOOKUPVALUE(Sales[Sales Amount];Sales[Date]; DATEADD(Sales[Date]; -1; YEAR))

[Anonymous]

We don't have Date field in Fact Sales. Below ERD and Sample Data will Explain things well.

Dim Date			
Date_Key	Date	        Year	Month
20180301	2018-03-01	2018	3
20180302	2018-03-02	2018	3
20180303	2018-03-03	2018	3
20180304	2018-03-04	2018	3
20170301	2017-03-01	2017	3
20170302	2017-03-02	2017	3
20170303	2017-03-03	2017	3
20170304	2017-03-04	2017	3

Fact_Sales		
Date_Key	Sales_Amount             Offer Flag
20180301	125	                      1
20180302	145	                      1
20180303	56	                      1
20180304	789	                      1
20170301	145	                     -1
20170302	1	                     -1
20170303	25	                     -1
20170304	90	                    125

Suppose Current Year is 2018 and we will select four rows where Flag<>-1 and we will display Sales of 2017 with same dates. For Last year we will not consider Flag. Dates can be randon in a month where flag<>-1. so Date range will not work for us. We have to compare Exact Dates. Please tell what else you needs more from my side. Appreaciate your quick response.

[v-jiascu-msft · Accepted solution]

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

[Anonymous]

@v-jiascu-msft Thanks for your reply. For last year we will not apply Flag Filter. SameperiodLastYear will not work IF user select Month and it will calculate all Dates of month of Last Year. let explain more.

for Expamle we have current month sales offer for 6 random days on a item on dates
Dates                 Sales     Flag
09-01-2018        25          1

09-02-2018       125         1

09-03-2018        45          1

09-15-2018       14           1

09-06-2018       14           1

09-07-2018       12           1

we needs to have same Dates with one year back in 2017.

Dates                 Sales    
09-01-2017        10

09-02-2017       12         

09-03-2017       145          

09-15-2017       114           

09-06-2017       14           

09-07-2017       10

Please let me know if you needs any more from my side.           

[v-jiascu-msft]

Hi @Anonymous,

The function SameperiodLastYear will work because the same period depends on your current context. What's the error in my demo? Please point out. Then we can talk based on the same data. 

Best Regards,

Dale

[Anonymous]

I have different measures at the Dashboard. e.g Meaures Sales with Offer This Year:= Calculate(Sum(Sales Amount), Offer Flag<>-1). This will give me correct Values in every contex But for Prevous year Calculation I needs Calculates Sales Amount for same dates one year back and we will not apply Offer Filter for LY Measure. Please tell me where you are calculating Offer Falg<>-1 for Current Year.

[v-jiascu-msft]

Hi @Anonymous,

Please download my demo and you can see the details. I think it's the step in blue.

Measure =
IF (
    MIN ( Fact_Sales[Offer Flag] ) = -1, //This step.
    BLANK (),
    CALCULATE (
        SUM ( Fact_Sales[Sales_Amount] ),
        SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
    )
)

Best Regards,

Dale

[Anonymous]

Thanks Dale for your reply,

Your solution is very close to what i am required.  We are already calculating LY Sale Measure. I try below with Existing Date Table.

Sales.LY During Offer Test:=
If(MIN(Sales[Offer Flag])=-1,BLANK(), CALCULATE([Sales LY])). When I put this in Card Visual It give me wrong result But when i put this in Table and put Dates on Rows it gives me only dates where Offer Flag <>-1 but total amount was wrong. To fix this I apply Visual level Filter on table of (Sales.LY During Offer Test is not Blank). Then I get correct result.  but when I remove Date from Table again it give me wrong result.
I have question,  Why we need Extra Date table? Can we do this without extra Date table.

I want to display Value in Card Visual. Let me try same like your Demo to create new Calendar date table. Then I will mark this as Solution.

Many Thanks indeed.

Azhar

[Anonymous]

Dear Dale,

I tried same model as of you in My SSAS Tabular Cube.  I created New Date Table with just two columns from exsiting View of date. I mark this table as Date in model But My Measure TestMeasure:= CALCULATE(min('Offer Dates'[Date]),SAMEPERIODLASTYEAR('Offer Dates'[Date])) Always give me the first Date. So this is not working. Further Can you please add Year in Slicer from Dim_Date from in your demo PBIX and see Measure Values are disappeared.
What I am missing here please and why SamePeriodLastYear is not working.

[v-jiascu-msft]

Hi @Anonymous,

The SAMEPERIODLASTYEAR is a time intelligence function that needs a complete date table. There isn't one in the demo, so I created one. 

Can you please point out which part is wrong in the DEMO? Please also post what the result should be based on the demo. To be honest, the result in the demo is right and it can be verified. 

The measure always needs the context to evaluate. How did you test it in the SSAS? For example, 

Date                SAMEPERIODLASTYEAR

2018-01-10     2017-01-10          if there isn't a date 2018-01-10, how does the formula know which year is last year.

Best Regards,
Dale

[Anuradha]

Use DATEADD function witht the YEAR. 

Sales.LY = CALCULATE([Sales], DATEADD(Subscribers[Sale Date].[Date], -1, YEAR))