Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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
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!
@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.
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 |
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.
Trying to combine the result of Prev year and current Year values per month!
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
69 |
User | Count |
---|---|
114 | |
100 | |
97 | |
72 | |
68 |