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

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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

I have different measures at the Dashboard. e.g Meaures Sales with Offer This Year:= Calculate(Sum(Sales Amount), Offer Flag<>-1). This will give me correct Values in every contex But for Prevous year Calculation I needs Calculates Sales Amount for same dates one year back and we will not apply Offer Filter for LY Measure. Please tell me where you are calculating Offer Falg<>-1 for Current Year.

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

    Hi Anonymous,

     

    Please download my demo and you can see the details. I think it's the step in blue.

    Measure =
IF (
    MIN ( Fact_Sales[Offer Flag] ) = -1, //This step.
    BLANK (),
    CALCULATE (
        SUM ( Fact_Sales[Sales_Amount] ),
        SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
    )
)

     

    Best Regards,

    Dale

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks Dale for your reply,

    Your solution is very close to what i am required.  We are already calculating LY Sale Measure. I try below with Existing Date Table.

    Sales.LY During Offer Test:=
    If(MIN(Sales[Offer Flag])=-1,BLANK(), CALCULATE([Sales LY])). When I put this in Card Visual It give me wrong result But when i put this in Table and put Dates on Rows it gives me only dates where Offer Flag <>-1 but total amount was wrong. To fix this I apply Visual level Filter on table of (Sales.LY During Offer Test is not Blank). Then I get correct result.  but when I remove Date from Table again it give me wrong result.
    I have question,  Why we need Extra Date table? Can we do this without extra Date table.

    I want to display Value in Card Visual. Let me try same like your Demo to create new Calendar date table. Then I will mark this as Solution.

    Many Thanks indeed.

     

    Azhar

  • 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