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

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

 

 

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

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      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

        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.