cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kkalyanrr
Helper IV
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:

YearMonthOpening BalanceClosingBalanceVariance
FY19-20125000145000-20000


DataSet:

YearMonthOpening BalanceClosingBalance
20190712500085000
201908135000115000
201909145000115000
201910115000145000
201911115000135000
20191285000125000
20200112500085000
202002135000115000
202003145000115000
202004115000145000
202005115000135000
20200685000145000




1 ACCEPTED SOLUTION
Eyelyn9
Community Support
Community Support

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:

12.24.2.1.PNG

 

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.

View solution in original post

2 REPLIES 2
Eyelyn9
Community Support
Community Support

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:

12.24.2.1.PNG

 

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.

View solution in original post

vanessafvg
Super User I
Super User I

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors