Forum Discussion
Summarize for SAMEPERIODLASTYEAR() not working
I am summing CY to SPLY Data , but data disapears!
NUM=
Var _Union=
GROUPBY(
UNION(
SUMMARIZE('TBL',[GP],[ID],[DATE], "@CTCS",CALCULATE(SUMX('TBL',[MBR_CTCS]),SAMEPERIODLASTYEAR('Calendar'[Date]))),
SUMMARIZE('TBL',[GP],[ID],[DATE], "@CTCS",CALCULATE(SUMX('TBL',[MBR_CTCS])))
),
[GP],[ID],[DATE], "_CTCS", SUMX(CURRENTGROUP(),[@CTCS]))
RETURN
SUMX(_Union,IF([_CTCS] > 1,1,0))
My result
Expected result:
How to re-write SAMEPERIODLASTYEAR() within Summarize function? Thanks!
Hi NilR ,
Here are the steps you can follow：
1. Create measure.
Measure = IF( YEAR(MAX('Table'[DATE]))=YEAR(TODAY()), SUMX( FILTER(ALL('Table'), 'Table'[ID]=MAX('Table'[ID])&& YEAR('Table'[DATE])=YEAR(MAX('Table'[DATE]))-1&& MONTH('Table'[DATE])=MONTH(MAX('Table'[DATE]))),[MBR_CTCS]) + MAX('Table'[MBR_CTCS]),BLANK())2. Result:
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
8 Replies
NilR , all time intelligence fucntion work on Filter/row context. So when you use them in calculated data table where filter context and row context can not apply , it take the last date and start building the data.
So not the best function to use in in table
In table you can add column like
New column =
var _date = [Date]
return
sumx(filter(Table, Table[Date] = date(Year(_date)-1, month(_date), day(_date) )) , [Value])
Time Intelligent is set and working for Current Selected year, but I need to summarize and the SPLY function is not working in Summarize!
Hi,
Share some data, exlplain the question and show the expected result.
Hi NilR ,
Here are the steps you can follow：
1. Create measure.
Measure = IF( YEAR(MAX('Table'[DATE]))=YEAR(TODAY()), SUMX( FILTER(ALL('Table'), 'Table'[ID]=MAX('Table'[ID])&& YEAR('Table'[DATE])=YEAR(MAX('Table'[DATE]))-1&& MONTH('Table'[DATE])=MONTH(MAX('Table'[DATE]))),[MBR_CTCS]) + MAX('Table'[MBR_CTCS]),BLANK())2. Result:
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
very smart, thank you!
