March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.... 😞
Solved! Go to Solution.
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]))
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
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
Hi Frank,
Data:
https://www.dropbox.com/s/9zxtoauleo7v2cb/test%20date%20-%20ytd.pbix?dl=0
Data edited (deleted data in sample data):
Thanks!
BoB
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
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
I edited the file: deleted all data >11-06 so the figure shows the date like the way I prefer 🙂
thanks again!
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'
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):
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
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
87 | |
85 | |
76 | |
49 |
User | Count |
---|---|
163 | |
148 | |
103 | |
74 | |
55 |