cancel
Showing results for
Did you mean:
Helper IV

## Picking the first and last months of Fiscal Year

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

1 ACCEPTED SOLUTION
Community Support

Hi @kkalyanrr ,

Please use the following formula to create a new table:

``````Table 2 =
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.

2 REPLIES 2
Community Support

Hi @kkalyanrr ,

Please use the following formula to create a new table:

``````Table 2 =
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.

Super User I

Proud to be a Super User!

Announcements