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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Dear Dale,

I tried same model as of you in My SSAS Tabular Cube.  I created New Date Table with just two columns from exsiting View of date. I mark this table as Date in model But My Measure TestMeasure:= CALCULATE(min('Offer Dates'[Date]),SAMEPERIODLASTYEAR('Offer Dates'[Date])) Always give me the first Date. So this is not working. Further Can you please add Year in Slicer from Dim_Date from in your demo PBIX and see Measure Values are disappeared.
What I am missing here please and why SamePeriodLastYear is not working.

 

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

Hi Anonymous,

 

The SAMEPERIODLASTYEAR is a time intelligence function that needs a complete date table. There isn't one in the demo, so I created one. 

Can you please point out which part is wrong in the DEMO? Please also post what the result should be based on the demo. To be honest, the result in the demo is right and it can be verified. 

The measure always needs the context to evaluate. How did you test it in the SSAS? For example, 

Date                SAMEPERIODLASTYEAR

2018-01-10     2017-01-10          if there isn't a date 2018-01-10, how does the formula know which year is last year.

 

 

Best Regards,
Dale