Forum Discussion
Advocate IYTD row in table
Hello,
I have a table with 3 columns in Power Bi: Month, Sales 22, Sales 23. I want to add a new row at the end of the table that only adds the Year to Month data. Here is an example what i need for the month Januar until Oktober:
| Month | Sales 22 | Sales 23 |
| January | 10 | 20 |
| February | 11 | 21 |
| March | 12 | 22 |
| April | 13 | 23 |
| May | 14 | 24 |
| June | 15 | 25 |
| July | 16 | 26 |
| August | 17 | 27 |
| September | 18 | 28 |
| Oktober | 19 | 29 |
| November | 20 | |
| December | 21 | |
| Total | 186 | 245 |
| YTD | 145 | 245 |
For the table, I already have 2 mesuars for sales 22 and sales 23. The sales table is linked to a timetable whose months are in column 1. Can anyone help me?
Thanks!
Kerstin
Hi SchwarzK ,
I created some data:
Date Table:
Main Table:
Here are the steps you can follow：
Create calculated table.
Table = var _table1= DISTINCT('Date'[Month]) var _table2= {"YTD"} return UNION( _table1,_table2)Create measure.
Sales 22 = var _today=TODAY() return IF( NOT(HASONEVALUE('Table'[Month])), SUMX( FILTER(ALL('Main Table'),YEAR('Main Table'[Date])=YEAR(_today)-1),[Sales]), IF( HASONEVALUE('Table'[Month])&&MAX('Table'[Month])<>"YTD", SUMX( FILTER(ALL('Main Table'), YEAR('Main Table'[Date])=YEAR(_today)-1&&'Main Table'[Month]=MAX('Table'[Month])),[Sales]), SUMX( FILTER(ALL('Main Table'), 'Main Table'[Date]>=DATE(YEAR(_today)-1,1,1)&&'Main Table'[Date]<=DATE(YEAR(_today)-1,MONTH(_today),DAY(_today))),[Sales]) ))Sales 23 = var _today=TODAY() return IF( NOT(HASONEVALUE('Table'[Month])), SUMX( FILTER(ALL('Main Table'),YEAR('Main Table'[Date])=YEAR(_today)),[Sales]), IF( HASONEVALUE('Table'[Month])&&MAX('Table'[Month])<>"YTD", SUMX( FILTER(ALL('Main Table'), YEAR('Main Table'[Date])=YEAR(_today)&&'Main Table'[Month]=MAX('Table'[Month])),[Sales]), SUMX( FILTER(ALL('Main Table'), 'Main Table'[Date]>=DATE(YEAR(_today),1,1)&&'Main Table'[Date]<=DATE(YEAR(_today),MONTH(_today),DAY(_today))),[Sales]) ))Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
4 Replies
- lbendlin
Super User
1. add a calculated column to your calendar table to flag if a date in the past was before or after the current date minus one year.
isPastPY = [Date]<=EDATE(TODAY(),-12)
2. add that as a filter to your SAMEPERIODLASTYEAR filter in your CALCULATE.
CALCULATE(SUM(Sales[Sales],SAMEPERIODLASTYEAR(Dates[Date]),isPastPY)
Hi SchwarzK ,
I created some data:
Date Table:
Main Table:
Here are the steps you can follow：
Create calculated table.
Table = var _table1= DISTINCT('Date'[Month]) var _table2= {"YTD"} return UNION( _table1,_table2)Create measure.
Sales 22 = var _today=TODAY() return IF( NOT(HASONEVALUE('Table'[Month])), SUMX( FILTER(ALL('Main Table'),YEAR('Main Table'[Date])=YEAR(_today)-1),[Sales]), IF( HASONEVALUE('Table'[Month])&&MAX('Table'[Month])<>"YTD", SUMX( FILTER(ALL('Main Table'), YEAR('Main Table'[Date])=YEAR(_today)-1&&'Main Table'[Month]=MAX('Table'[Month])),[Sales]), SUMX( FILTER(ALL('Main Table'), 'Main Table'[Date]>=DATE(YEAR(_today)-1,1,1)&&'Main Table'[Date]<=DATE(YEAR(_today)-1,MONTH(_today),DAY(_today))),[Sales]) ))Sales 23 = var _today=TODAY() return IF( NOT(HASONEVALUE('Table'[Month])), SUMX( FILTER(ALL('Main Table'),YEAR('Main Table'[Date])=YEAR(_today)),[Sales]), IF( HASONEVALUE('Table'[Month])&&MAX('Table'[Month])<>"YTD", SUMX( FILTER(ALL('Main Table'), YEAR('Main Table'[Date])=YEAR(_today)&&'Main Table'[Month]=MAX('Table'[Month])),[Sales]), SUMX( FILTER(ALL('Main Table'), 'Main Table'[Date]>=DATE(YEAR(_today),1,1)&&'Main Table'[Date]<=DATE(YEAR(_today),MONTH(_today),DAY(_today))),[Sales]) ))Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
SchwarzKAnonymousThank you so much! It works perfectly 🙂
- SchwarzK
Hi Anonymous
I still have a problem. I need the sales values for a modified financial year. My year is not a normal calendar year, but starts in July and ends in June of the following year. I need the sales values from January of the next calendar year: Instead of this table
Month Sales 22 Sales 23 January 10 20 February 11 21 March 12 22 April 13 23 May 14 24 June 15 25 July 16 26 August 17 27 September 18 28 Oktober 19 29 November 20 December 21 Total 186 245 YTD 145 245 I need:
Month Sales 22_23 (July 22 Juni 23) Sales 23_24 (July 23 Juni 24) July 16 26 August 17 27 September 18 28 Oktober 19 29 November 20 December 21 January 20 February 21 March 22 April 23 May 24 June 25 Total 246 110 YTD 70 110 Thanks a lot for your help.
Best regards
Kerstin
