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

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.           

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        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