Picking the first and last months of Fiscal Year

[kkalyanrr]

Hello,

I'm trying to present the data to my team in the following fashion, can you help me with the DAX.

Report Pattern:

YearMonth	Opening Balance	ClosingBalance	Variance
FY19-20	125000	145000	-20000

DataSet:

YearMonth	Opening Balance	ClosingBalance
201907	125000	85000
201908	135000	115000
201909	145000	115000
201910	115000	145000
201911	115000	135000
201912	85000	125000
202001	125000	85000
202002	135000	115000
202003	145000	115000
202004	115000	145000
202005	115000	135000
202006	85000	145000

[v-eqin-msft · Accepted solution]

Hi @kkalyanrr ,

Sorry for my late reply...

Please use the following formula to create a new table:

Table 2 =
ADDCOLUMNS (
    SUMMARIZE (
        'Table',
        "YearMonth",
            "FY" & MIN ( 'Table'[Year] ) & "-"
                & MAX ( 'Table'[Year] ),
        "Opening Balance",
            MINX (
                FILTER ( 'Table', 'Table'[YearMonth] = MIN ( 'Table'[YearMonth] ) ),
                [Opening Balance]
            ),
        "ClosingBalance",
            MAXX (
                FILTER ( 'Table', 'Table'[YearMonth] = MAX ( 'Table'[YearMonth] ) ),
                [ClosingBalance]
            )
    ),
    "w", [Opening Balance] - [ClosingBalance]
)

The final output looks like this:

Please take a look at the pbix here.

Best Regards,
Eyelyn Qin

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

[vanessafvg]

will this help?  https://www.youtube.com/watch?v=XjAMee2LhKE