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,

 

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

 

Regards,

Frank

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

    Hi BoBBie,

     

    Please refer to the new measures.

     

     

    ytd = CALCULATE(COUNTA(Blad4[orderno]),FILTER(ALL(Blad4),Blad4[Year]<=MAX(Blad4[Year])))
    ytd-1-new = [ytd]-COUNTA(Blad4[orderno])

     

    For more details, please check the pbix as attached.

     

    Regards,

    Frank

     

    • BoBBie's avatar
      BoBBie
      Frequent Visitor
      7 years ago

      Hi v-frfei-msft Frank,

       

      thanks again. 

      It's not exacty what i'm looking for. Maybe I misinterpet the ytd function......

       

      The file: https://www.dropbox.com/s/9zxtoauleo7v2cb/test%20date%20-%20ytd.pbix?dl=0 

      is the original file. 

       I) Why does PBI shows '2019' (YTD-1 (sameperiodlastyear) while there is no '2019' data? (my fault....using 'previous year' and date table so data year-1 which is 2018 for 2019....)

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

       

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

      I edited the file: deleted all data >11-06 so the figure shows the date like the way I prefer :-)

       

      thanks again!

      BoB

      • BoBBie's avatar
        BoBBie
        Frequent Visitor
        7 years ago

        Hi v-frfei-msft,

         

        Can you please help me....? I'm getting desperate...... ;-)

         

        thanks!

        BoB