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]))
parry2k's avatar
parry2k
Super User
7 years ago

BoBBie try following two measures and I guess this will get you the result

 

first calculated the all time last order date:

 

Last Order Date = CALCULATE( max( Blad1[orderdate] ), ALL( Blad1 ) )

Create last ytd order count based on last order date, in this measure we will calculate the date for each year and filter orders until that date, feel free to change the measure to meet you need.

 

ytd-2 = 
VAR currentYear  = MAX( 'date table'[year] ) 
VAR currentYearLastDate = DATE(currentYear, MONTH( [Last Order Date] ), day( [Last Order Date] ) )
RETURN 
    CALCULATE( [ytd], 'date table'[Date] <= currentYearLastDate )
BoBBie's avatar
BoBBie
Frequent Visitor
7 years ago

Hi parry2k,

 

thanks for help!

Unfortenately is is not working correctly.....

I think the solution is a filter for all years (current and previous years) in the month and day of the latest orderdate......

The Y-1 is only for visual comparison in the column chart...

 

I'm new with PBI & DAX so it's a bit trial and error (a).

 

BoB

 

  • BoBBie's avatar
    BoBBie
    Frequent Visitor
    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]))
  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    BoBBie based on the question you posted, it si working as expected. see attached.

     

     

  • BoBBie's avatar
    BoBBie
    Frequent Visitor
    7 years ago

    Hi parry2k,

     

    Almost......the YTD-2 numbers are 'fixed' 

     

     

     

     

    So I can't drill down in the chart column.

     

    In this file I have edited the date (deleted data month & date > last order date 11-6-18 in sample data):

    https://www.dropbox.com/s/2vvbmz0gt1evfdl/test%20date%20-%20ytd%20%5Bedit%20sample%20data%29.pbix?dl...

    and this is how it should work.....

     

    The latest day in the sample data is 11-06-18 (11 june 18) --> what I need is that all data previouws years is the same period in the corresponding years:

      - 01-01-18 - 11-06-18

      - 01-01-17 - 11-06-17

      - 01-01-16 - 11-06-16

      - 01-01-15 - 11-06-15

     please note: that the actual data-set is updated daily (data till day before today) so the 'latest actual date' will be the day before today

     

    thanks again!

    BoB