Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 ...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

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

    Needs_to_have_same_dates_in_Previous_Year_Using_DAX

     

    Best Regards,

    Dale

Anuradha's avatar
Anuradha
Frequent Visitor
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    Anuradha
    Frequent Visitor
    8 years ago

    Share some sample data/pbix

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Anuradha
        Frequent Visitor
        8 years ago

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