Using Group By and Max + Sum function

[Anonymous]

Please help, I've table below.

PJ_CODE	Questionnaire	SortQ	Answer	SortA	Questionnaire_Type	Customer_ID	TotalAnswer	Activity_Date
J005	Purpose	05	C	0503	Visit	381630	1	11/12/2023
J005	Purpose	05	A	0504	Visit	427355	0.5	11/12/2023
J005	Purpose	05	B	0501	Visit	427355	0.5	11/12/2023
J005	Purpose	05	B	0501	Visit	188237	1	10/12/2023
J005	Purpose	05	A	0504	Visit	426766	0.5	09/12/2023
J005	Purpose	05	C	0503	Visit	426766	0.5	09/12/2023
J005	Purpose	05	B	0501	Visit	402564	1	08/12/2023
J005	Purpose	05	B	0501	Visit	427473	1	07/12/2023
J005	Purpose	05	B	0501	Visit	426955	1	05/12/2023
J005	Purpose	05	B	0501	Visit	427441	1	05/12/2023
J005	Purpose	05	A	0504	Visit	426766	0.5	04/12/2023
J005	Purpose	05	C	0503	Visit	426766	0.5	04/12/2023

and I want to create dax measure about visualize matrix to group by Answer and sum of TotalAnswer and order by the MAX of Activity_Date.

My Expected of Matrix below.

Group by Answer and order by the MAX of Activity_Date.

Answer	Sum of TotalAnswer
A	1
B	5.5
C	1.5
Total	8

As picture below, I've already create relationship of TEMP_WQ. 

I use such a

Dax:
Previous1 =
VAR StartDate = MIN(DimDate[Date]) /* User can select Date as (Number 2) */
VAR EndDate = MAX(DimDate[Date]) /* User can select Date as (Number 2) */
VAR NumDays = MAX(0, EndDate - StartDate + 1) /* Count of date that selected */
VAR PreviousStartDate = StartDate - NumDays /* Need to focus of PreviosDate */
VAR PreviousEndDate = EndDate - NumDays /* Need to focus of PreviosDate */
Var PJ = VALUES(DimProject[PJ_CODE]) /* User can select Project as (Number 1) */
Return CALCULATE(SUM(TEMP_WQ[TotalAnswer])

,GROUPBY(TEMP_WQ,TEMP_WQ[Questionnaire_Type],TEMP_WQ[Questionnaire],TEMP_WQ[Answer]),
FILTER(
ALL(TEMP_WQ),
TEMP_WQ[Activity_Date] >= PreviousStartDate &&
TEMP_WQ[Activity_Date] <= PreviousEndDate &&
TEMP_WQ[PJ_CODE] IN PJ &&
TEMP_WQ[Questionnaire_Type] = "Visit"
)
)

But Result was wrong.

Answer	Sum of TotalAnswer
B	5.5
Total	5.5

[Anonymous]

Hi @Anonymous ,

Please try:

SumTotalAnswer = SUM('TEMP_WQ'[TotalAnswer])

MaxActivityDate = MAX('TEMP_WQ'[Activity_Date])

I do not quite understand "order by the MAX of Activity_Date.".

I realized your expected result.

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Anonymous]

Hi @Anonymous ,

Thank you for your help. I tried the following DAX expression for the Previous1.

Previous1 = 
Var PJ = VALUES(DimProject[PJ_CODE]) 
Return CALCULATE(SUM(TEMP_WQ[TotalAnswer])
,GROUPBY(TEMP_WQ,TEMP_WQ[Questionnaire_Type],TEMP_WQ[Questionnaire],TEMP_WQ[Answer]),
FILTER(
ALL(TEMP_WQ),
TEMP_WQ[Activity_Date] >= PreviousStartDate &&
TEMP_WQ[Activity_Date] <= PreviousEndDate &&
TEMP_WQ[PJ_CODE] IN PJ &&
TEMP_WQ[Questionnaire_Type] = "Visit")
)

But this didnt work.

Answer	Previos1
B	5.5
Total	5.5

My Expected Result.

Answer	Previos1
A	1
B	5.5
C	1.5
Total	8

So, I try to create SQL script to expain my expected.

Sub TableMy Expected Result

If you have any question please feel free to contact me.

Niramol L.