Forum Discussion
Advocate IYTD row in table
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
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
Advocate IHi 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