Forum Discussion

BoBBie's avatar
BoBBie
Frequent Visitor
7 years ago
Solved

ytd multipe years

I have a database with all sales data from 2012 (inclusing a seperate date table). I want to display the sales orders amount of current year to date (data is updated / synct daily) including all previous years.

I have created 2 measures:

 

Orders YTD-0 = CALCULATE (TOTALYTD([Total orders]; 'Date_table'[date])) 

Orders YTD-1 = CALCULATE([Orders YTD-0];SAMEPERIODLASTYEAR('Date_table'[date]))

 

unfortenately only amount of current year (2018) and previous year (2017) are correct (YTD).

All other previous years shows full years amount.... :-(

 

  • BoBBie's avatar
    BoBBie
    7 years ago

    parry2k, v-frfei-msft,

     

    solved by searching the community YTD untill today thanks to OwenAuger

     

     

     

    YTD Values till last orderdate = 
VAR LOD = [Last Order Date]
VAR TodayMonth =
    MONTH ( LOD )
VAR TodayDay =
    DAY ( LOD )
VAR YearDateFilter =
    GENERATE (
        VALUES ( 'date table'[year] );
        VAR TodayInCurrentYear =
            DATE ( 'date table'[year] ; TodayMonth; TodayDay )
        RETURN
            CALCULATETABLE (
                DATESYTD ( 'date table'[Date] );
                TREATAS ( { TodayInCurrentYear }; 'date table'[Date] )
            )
    )
RETURN
    CALCULATE ( DISTINCTCOUNT(Blad1[orderno]); KEEPFILTERS ( YearDateFilter ) )

    and

     

     

    YTD-PY values till last order date = CALCULATE([YTD Values till last orderdate];SAMEPERIODLASTYEAR('date table'[Date]))

14 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi BoBBie,

     

    I made one sample for your reference. If it doesn't meet your requirement, kindly share your sample data and excepted result to me.

     

    1. Create a date table.

    date = CALENDARAUTO()

    2. Create the measures as below.

     

    YTD = TOTALYTD(SUM(Table1[sales]),Table1[date])
    ytd-1 = CALCULATE([YTD],SAMEPERIODLASTYEAR(Table1[date]))

     

    For more details, please check the pbix as attached.

     

    Regards,

    Frank

    • BoBBie's avatar
      BoBBie
      Frequent Visitor
      7 years ago

      Hi Frank,

       

      thanks for reply!

       

      Your sample-date only have data in one month..... My data containts lots of daily data / transactions from 2011 till today.

      What I need is to show the data of previouws years ONLY for exact the same period.

      So I can compare the selected periods clearly. 

       

      e.g.:

                  2015     2016    2017    2018

      jan         100      150       160     100

      feb         250      275       300     100

      mar        300      325       400     200 

      apr         100        90         50     100

      may       200      210        260     200

      jun         150      175        100       50 

      jul          200      225        300     250

      aug          50        75          50    100

      sep           50        40       100     150  

      oct           50         20       100      25

      nov        100       150         50 

      dec        200       200         75

       

      total:    1.650   1.935     1.945    1.275       

      total:   1.350  1.585    1.820    1.275

       

      now previous years shows data of whole year.... 

      (i'm sorry, I made an exampe file but can not attach it here)

      • v-frfei-msft's avatar
        v-frfei-msft
        Community Support
        7 years ago

        Hi BoBBie,

         

        You can upload your sample file to dropbox and share the link here.

         

        Regards,

        Frank