Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
BoBBie
Frequent Visitor

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.... 😞

 

BI.png

1 ACCEPTED SOLUTION
BoBBie
Frequent Visitor

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

View solution in original post

14 REPLIES 14
v-frfei-msft
Community Support
Community Support

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

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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)

Hi @BoBBie,

 

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

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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...

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

 

thanks again!

BoB

BoBBie
Frequent Visitor

Hi @v-frfei-msft,

 

Can you please help me....? I'm getting desperate...... 😉

 

thanks!

BoB

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


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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 based on the question you posted, it si working as expected. see attached.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

attached now



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi @parry2k,

 

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

 

 

 

BI2.png

 

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
Frequent Visitor

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

attached now



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.