Summarize for SAMEPERIODLASTYEAR() not working

[NilR]

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!

[v-yangliu-msft · Accepted solution]

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

[NilR]

very smart, thank you!

[amitchandak]

@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])

[NilR]

Time Intelligent is set and working for Current Selected year, but I need to summarize and the SPLY function is not working in Summarize!

[Ashish_Mathur]

Hi,

Share some data, exlplain the question and show the expected result.

[NilR]

Thanks for helping:

GP	ID	DATE	MBR_CTCS
360	0072	01/01/2022	1
360	0072	02/01/2022	1
360	0072	03/01/2022	0
360	0072	04/01/2022	1
360	0072	05/01/2022	1
360	0072	06/01/2022	1
360	0072	07/01/2022	1
360	0072	08/01/2022	1
360	0072	09/01/2022	1
360	0072	10/01/2022	1
360	0072	11/01/2022	1
360	0072	12/01/2022	1
360	0072	01/01/2023	1
360	0072	02/01/2023	1
360	0072	03/01/2023	1
360	0072	04/01/2023	1
360	0072	05/01/2023	1
360	0106	01/01/2022	1
360	0106	02/01/2022	1
360	0106	03/01/2022	1
360	0106	04/01/2022	1
360	0106	05/01/2022	1
360	0106	01/01/2023	1
360	0106	02/01/2023	1
360	0106	03/01/2023	1
360	0106	04/01/2023	1
360	0106	05/01/2023	1

[Ashish_Mathur]

Hi,

I read your original post and have just not been able to understand your requirement.  Please explain the question clearly and show the the expected result.

[NilR]

Trying to combine the result of Prev year and current Year values per month!