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]))
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 

 

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]))