Forum Discussion
SchwarzK
Advocate I
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 ne...
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
SchwarzKAdvocate I
Advocate IAnonymousThank you so much! It works perfectly 🙂