Forum Discussion

GGDAC's avatar
GGDAC
Helper I
6 years ago
Solved

Last Year Total

I have 5 years sales figures month wise I used PBI quick measure of Total YTD to calculate year to date figures by month and year I am using PBi time intelligence for dates My year and month select...
  • amitchandak's avatar
    6 years ago

    You can use dateytd and totalytd or trailing measure with calendar date

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
    This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))
    
    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
    Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
    Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
    
    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
    2 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Year))
    

    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 :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

  • v-lili6-msft's avatar
    6 years ago

    hi  GGDAC 

    For your case, simple way is that use SAMEPERIODLASTYEAR Function to create a measure as below:

    SAMEPERIODLASTYEAR = CALCULATE([Total YTD], SAMEPERIODLASTYEAR(‘Calendar’ [Date]))

    Please refer to this blog:

    https://databear.com/power-bi-dax-sameperiodlastyear-paralellperiod-and-dateadd/

    and 

    https://radacad.com/do-you-need-a-date-dimension

     

    By the way, for your [Total YTD] measure is created by quick measure, it should be like below:

    Total YTD =
    IF(
        ISFILTERED('Calendar'[Date]),
        ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
        TOTALYTD(SUM('Table'[Goal]), 'Calendar'[Date].[Date])
    )
    so I would suggest you adjust it as below:
    Total YTD = TOTALYTD(SUM('Table'[Goal]), 'Calendar'[Date])
     
     

    Regards,

    Lin

     

  • GGDAC's avatar
    GGDAC
    6 years ago

    Thanks Lin

    Sameperiodlastyear solved the issue

     

    Thanks once again for the support