Forum Discussion

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

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

Share some sample data/pbix

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 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
      7 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))

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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.