Removing filter context for one row

[Anonymous]

Hi All, 

I have sample data that looks like this n(please see below). What i need to do is create a new column that brings in the values in the Amoutn column, HOWEVER, for the month of Jan-22, the calculation should be Total Location Budget (which is $3,054,444 - this is just a static number) minus the sum of Amount from Jul-21 to Dec-21. 

i.e. 3, 054, 444 - 129,715 (129,715 should be the sum of Amount column from Jul21 to Dec21). Then after Jan22, all the values for this fiscal year should be blank. 

In my data table, i added in a month num column and created a tag that tags the row as 1 if year = current calendar year (i.e. 2021) and 2 if year = next calendar year (i.e. 2022). I then wrote a If statement that essentially said IF month num = 1 and calendar year num = 2, then total budget - sum of amount otherwise, just return Amount. 

But this is being impacted by date at the moment  (i.e. aggregation happening at the month level). How do i remove the filter context only for this one row? 

Current State

Store	Val Type	Month	Amount
Carinbar	BASELINE	Jul-21	166936.9
Carinbar	BASELINE	Aug-21	341130.9
Carinbar	BASELINE	Sep-21	261162
Carinbar	BASELINE	Oct-21	175974.6
Carinbar	BASELINE	Nov-21	175974.6
Carinbar	BASELINE	Dec-21	175974.6
Carinbar	BASELINE	Jan-22	175974.6
Carinbar	BASELINE	Feb-22	176516.1
Carinbar	BASELINE	Mar-22	81013.08
Carinbar	BASELINE	Apr-22	226615
Carinbar	BASELINE	May-22	265871.2
Carinbar	BASELINE	Jun-22	267826.7

Desired State

Store	Val Type	Month	Amount	Amount_Amended
Carinbar	BASELINE	Jul-21	166936.9	166936.9
Carinbar	BASELINE	Aug-21	341130.9	341130.9
Carinbar	BASELINE	Sep-21	261162	261162
Carinbar	BASELINE	Oct-21	175974.6	175974.6
Carinbar	BASELINE	Nov-21	175974.6	175974.6
Carinbar	BASELINE	Dec-21	175974.6	175974.6
Carinbar	BASELINE	Jan-22	175974.6	2,924,729
Carinbar	BASELINE	Feb-22	176516.1
Carinbar	BASELINE	Mar-22	81013.08
Carinbar	BASELINE	Apr-22	226615
Carinbar	BASELINE	May-22	265871.2
Carinbar	BASELINE	Jun-22	267826.7

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

You could create a column or measure.

1.column is below:

Column =
IF (
    YEAR ( [Month] ) = YEAR ( TODAY () ),
    [Amount],
    IF (
        MONTH ( [Month] ) = 1,
        3054444
            - CALCULATE (
                SUM ( [Amount] ),
                FILTER ( ALL ( 'Table' ), [Month] < EARLIER ( 'Table'[Month] ) )
            )
    )
)

2.measure is like below:

Measure =
IF (
    YEAR ( MAX ( [Month] ) ) = YEAR ( TODAY () ),
    SUM ( [Amount] ),
    IF (
        MONTH ( MAX ( [Month] ) ) = 1,
        CALCULATE (
            SUM ( [Amount] ),
            FILTER ( ALL ( 'Table' ), [Month] < MAX ( [Month] ) )
        )
    )
)

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Based on what I got so far.

if you can save your budget on the last date you can access it on all dates using CLOSINGBALANCEYEAR 

or

calculate(sum(Target[Budget]), filter(all('Date'), 'Date'[Year] = max('Date'[Year])) )

You can deal with target in a little bit different manner , see if that 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-Convert-to/ba-p/1476400

Distributing/Allocating the Yearly Target(Convert to Daily Target): https://community.powerbi.com/t5/Community-Blog/Distributing-Allocating-the-Yearly-Target-Convert-to-Daily/ba-p/1463290

[Anonymous]

Hi Amit, 

Thanks for that. I think my issue is mostly around not being able to remove filter context for Jan-22 numbers. 

For example, in my column, i want all numbers to be as is, but when we reach Jan-22, i want it to sum up $ amount from Jul21-Dec21. But the issue is that when i put it into a table, it's only summing up for the month of Jan-22 and i want it to sum all values PRIOR to Jan22 (starting from the financial year). 

[amitchandak]

@Anonymous , for that datesytd should help, but do you need that for all months ?

example , year-end date 6/30 means year start from 1-jul

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"6/30"))