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

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

     

     

    • 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
        8 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