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