Columns containing average value the current month[latest month in data source]& the previous month

[deb_power123]

Hi All,

Below is my input source table :-

AuditDate	Score	SchoolName	PercentageStudents
15.08.2021	-2	A	20%
15.08.2021	-1	B	30%
16.08.2021	3	C	22%
16.08.2021	0	D	45%
16.08.2021	-1	A	65%
17.08.2021	-1	B	17%
17.08.2021	-1	C	29%
18.08.2021	3	A	78%
18.08.2021	0	C	87%
19.08.2021	-1	C	22%
19.08.2021	3	D	45%
19.08.2021	0	E	65%
20.08.2021	-1	B	17%
21.08.2021	2	D	29%
22.08.2021	1	E	22%
22.08.2021	-1	A	45%
1.09.2021	0	E	65%
1.09.2021	-1	B	17%
2.09.2021	2	D	29%
3.09.2021	1	E	22%
3.09.2021	-1	A	45%
4.09.2021	1	C	65%
5.09.2021	1	C	17%
5.09.2021	3	A	29%

We have a aggregate calculated table with columns as  [Category],ParamScores[measures of the average and percentage column in source table] and Currentmonth as shown below.

NewTable = 
GENERATE (
    SUMMARIZE (
        SELECTCOLUMNS (
            'Table',
            "CurrentMonth", FORMAT ( 'Table'[AuditDate], "MMMM - yyyy" )
        ),
        [CurrentMonth]
    ),
    ADDCOLUMNS (
        DATATABLE (
            "Category", STRING,
            {
                { "AverageScore" },
                { "AveragePercentage" }
            }
        ),
        "ParamScore",
            VAR _m = [CurrentMonth]
            RETURN
                IF (
                    [Category] = "AverageScore",
                    FORMAT (
                        CALCULATE (
                            AVERAGE ( 'Table'[Score] ),
                            FORMAT ( 'Table'[AuditDate], "MMMM - yyyy" ) = _m
                        ),
                        ""
                    ),
                    FORMAT (
                        CALCULATE (
                            AVERAGE ( 'Table'[PercentageStudents] ),
                            FORMAT ( 'Table'[AuditDate], "MMMM - yyyy" ) = _m
                        ),
                        "0%"
                    )
                )
    )
)

Present Output:

Expected Output:

Assume in our sample data :  Current Month year is September 2021 and PreviousMonthYear is August 2021.

 I want to see the output as above.

[What modifications, is needed to my script to achieve this ?] or any other changes or new script. please suggest.

Kind regards

Sameer

[v-xiaoyan-msft]

Hi @deb_power123 ,

I'm trying to reproduce the problem as you provided it, do you expect the final result to be in the form of a table or can it be presented as a matrix?

Hope it helps,

Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[deb_power123]

Hi @v-xiaoyan-msft 

I am looking in form of a table in the above format..Its truly approeciable if you can provide any approach...