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 prev...
  • 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]))
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)