Always display year end target regardless of month selected

[johntakeda]

Hi there,

Looking for some help.  I'm trying to manipulate a matrix so that whichever month I have selected with my slicer, it will always show the final "year end Budget", similar to the below:

My year end is March (financial year).

The format of my input data is an excel table like the below:

When i load this data I transform and unpivot the date columns, so i'm left with just the one Budget column by cost center and date.

I suppose I could just create another excel file, where every month's data is the year end number, but I think there must be a more elegant way to do this.  Maybe some sort of measure that says IF the month is anything other than march, make it march...but i've no clue how this would look.

Any help is much appreciated.

Thanks,

John

[v-yanjiang-msft]

Hi @johntakeda ,

According to your description, I create a sample, and after unpivoting as you described, get this table.

Here's my solution, create a measure.

Year end budget =
SUMX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Date] = DATE ( 2023, 3, 1 )
            && 'Table'[Cost center] = MAX ( 'Table'[Cost center] )
    ),
    'Table'[Budget]
)

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[johntakeda]

This is really excellent, I had to add an extra line because I oversimplified in my original post, and there is an extra piece of data in there on whether the FTE are Own or Leased:

I just have one follow up, it gives a very strange result in the "total" for all Cost centres:

It seems like it just took the cost center with the largest number instead of the sum...any idea how to fix this?

Thanks,

John

[v-yanjiang-msft · Accepted solution]

Hi @johntakeda ，

A calculated column can work fine in the total.

Year end budget =
DIVIDE (
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] = DATE ( 2023, 3, 1 )
                && 'Table'[Cost center] = EARLIER ( 'Table'[Cost center] )
        ),
        'Table'[Budget]
    ),
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[Cost center] = EARLIER ( 'Table'[Cost center] )
                && 'Table'[Date] = EARLIER ( 'Table'[Date] )
        )
    )
)

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[johntakeda]

Fantastic - thanks for your help!

[amitchandak]

@johntakeda , refer if the use CLOSINGBALANCEYEAR  in this blog can help

Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...

[johntakeda]

Hi amitchandak,

The post is interesting, but not really relevant to my question.

I think I need some kind of measure that would return the data for the final month of the year regardless of which month i'm looking at, but I can't think how to build it.

Thanks,

John