Forum Discussion

nmhung49's avatar
nmhung49
Icon for Helper II rankHelper II
5 years ago
Solved

Caculate same period last year

I have Data table with date 01/01/2020 to 05/31/2021 and DimDate with date 01/01/2020 to 12/31/2021

When I create Dax Same Period Last Year for result 98 is incorrect. It is correct 13

Please help me the formula in this case

 

Thanks

 

 

 

Link File

 

 

Need Help

9 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    nmhung49 , I check out total of year 2020 is 98. As you have seleced year, That will give you year total.

     

    Also marked DimDate table as date table(option on right on table) and createcolumsn for Month, Yeat etc

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

    • nmhung49's avatar
      nmhung49
      Icon for Helper II rankHelper II
      5 years ago

      Thanks for you repply

      If slicer selected 2021 and Month not selected. I want Card Current Year show 30 and Card Same Period Last Year show 11

      Thanks 

       

       

    • nmhung49's avatar
      nmhung49
      Icon for Helper II rankHelper II
      5 years ago

      I'm sorry show 13 not 11, I want to Card 1 st show 30 and Card 2nd show 13 with (Picture #1) because Actual Data have Date 01/01/2020 to 31/05/2021 but DimDate have Date 01/01/2020 to 31/12/2021

      • ryan_mayu's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        5 years ago

        nmhung49 

        pls try this

        Measure = 
VAR _max=max('Table'[Date])
return sumx(FILTER('Table','Table'[Date]=_max),'Table'[sales])
  • Thejeswar's avatar
    Thejeswar
    Icon for Super User rankSuper User
    5 years ago

    Hi nmhung49 ,

    Change the SPLY formula as follows. Also I am not really sure, why you need a separte Date Dimension Table for this functionality. Change the formula to use Date from the same table as given below

     

    SPLY = CALCULATE(SUM(Data[Sale]),SAMEPERIODLASTYEAR(Data[Date]))
     
    This will change the Output from 98 to 13.
     
    Why do you expect to see 11..which the Sales for Same period last year is 13
    PFB screengrab

     

    • nmhung49's avatar
      nmhung49
      Icon for Helper II rankHelper II
      5 years ago

      ryan_mayu amitchandak 

      Sorry It is show 13, I separte DimDate Table because this is the primary key table that is related to other tables I use this table to control year, month, day

      • ryan_mayu's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        5 years ago

        nmhung49 

        Pls try this

        SPLY = 
VAR _MAX=MAX('Table'[Date])
RETURN CALCULATE(SUM('Table'[Sale]),FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(_MAX)-1&&MONTH('Table'[Date])<=MONTH(_MAX)))

        pls see the attachment below