Forum Discussion

reacharihant's avatar
reacharihant
Regular Visitor
9 years ago
Solved

Time Intelligence

Hello, I need to compare a date's count with the last year's date count.  I am using the following:  Calls YTD = CALCULATE(sum(CallCount[Count]),SAMEPERIODLASTYEAR(dCalendar[Date]))   but it gives...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi reacharihant,

    It will calculate running total of actuals when you use DATESYTD function in filter. Based on your description, the DATESYTD is not appropriate. Please create the current year's calls using the formula:


    Current Year Calls=SUM(CallCount[Count])


    SAMEPERIODLASTYEAR requires continuous date in the filter context. From your attachment, the date is not continuous, so you'd better create a Calendar table by clicking "New Table" under Modeling on Homepage. Please confirm you create a relationship between Calendar[date] and CallCount[date]. Then you can use the formula to create the last year's calls.

    Last Years Calls=CALCULATE(sum(CallCount[Count]),SAMEPERIODLASTYEAR(Calendar[Date]))


    Please check if there is a relationship between your fact table and Calendar Table. I test it using my sample table, it returns the expected result.



    Finally, create a natrix visual, select the CallCount[date] as rows, the [Last Years Calls] and [This Years Calls] as the values levels.

    Best Regards,
    Angelia

Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

I'm a bit confused by this, if you use the same formula in two different columns, you are likely to get the same result. You would need to have two columns/measures, one using SAMPERIODLASTYEAR most likely and one using DATESYTD.

 

https://msdn.microsoft.com/en-us/library/ee634221.aspx

 

Sample data would help.

  • reacharihant's avatar
    reacharihant
    Regular Visitor
    9 years ago

     

    Please find attached.

     

    The column Calls FYTD is coming blank. However the total below is coming correctly. I need to display the count of calls in the column Calls FYTD besides every last year's same date.

    • v-huizhn-msft's avatar
      v-huizhn-msft
      Microsoft Employee
      9 years ago

      Hi reacharihant,

      It will calculate running total of actuals when you use DATESYTD function in filter. Based on your description, the DATESYTD is not appropriate. Please create the current year's calls using the formula:


      Current Year Calls=SUM(CallCount[Count])


      SAMEPERIODLASTYEAR requires continuous date in the filter context. From your attachment, the date is not continuous, so you'd better create a Calendar table by clicking "New Table" under Modeling on Homepage. Please confirm you create a relationship between Calendar[date] and CallCount[date]. Then you can use the formula to create the last year's calls.

      Last Years Calls=CALCULATE(sum(CallCount[Count]),SAMEPERIODLASTYEAR(Calendar[Date]))


      Please check if there is a relationship between your fact table and Calendar Table. I test it using my sample table, it returns the expected result.



      Finally, create a natrix visual, select the CallCount[date] as rows, the [Last Years Calls] and [This Years Calls] as the values levels.

      Best Regards,
      Angelia

      • reacharihant's avatar
        reacharihant
        Regular Visitor
        9 years ago

        Thanks! it worked.