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 selections are in slicer

 

Now i want to calculate same period LY and I am struggling with right measure. Please help

 

AC

Need Help

15 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    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/

    • GGDAC's avatar
      GGDAC
      Helper I
      6 years ago

      Dear Amit

      I am too new to PBI so probably your solutions was too much for me to understand

      Just to explain a bit more to get specific measure

      I have slicer showing months and another slicer showing year. I created measure from Quick Measure in PBI on YTD amount

      and I am getting my numbers correct whenever I select given period

      Now I want to add another measure for Last Year so when I select period month and year, it also show me Last year numbers

       

      So if you could guide me with specific measure on that

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        GGDAC ,

        what you are using for YTD. The same can be use for last year Like I use datesytd for this year

        YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date])))

         

        The for last year I use dateadd to move it a year behind

        Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year)))

         

        As you can have non-continuous dates, and time intelligence need continuous dates most of the time we suggest date calendar.

         

        You have few others like sameperiodlastyear, totalytd ,previousyear that can help.

         

    • GGDAC's avatar
      GGDAC
      Helper I
      6 years ago

      Thank you Greg

      My model is something similar to what you sent in attachment.  I am new to this and I am trying to develop measure as suggested but it shows error. Please see below my measure and advise where I am going wrong.

      Here RE SALE YTD is quick measure taken from PBI which is basically my Sales figures.

      Cal.year / month is my calendar table in the field

       

      LY Same Period = CALCULATE('GS Customer'[RE SALE YTD],SAMEPERIODLASTYEAR('GS Customer'[Cal. year / month]))
  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    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
      Helper I
      6 years ago

      Thanks Lin

      Sameperiodlastyear solved the issue

       

      Thanks once again for the support