Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
Hi, @Andrea_Jess ;
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.
Hi, @Andrea_Jess ;
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.
@Andrea_Jess , 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-C...
Distributing/Allocating the Yearly Target(Convert to Daily Target): https://community.powerbi.com/t5/Community-Blog/Distributing-Allocating-the-Yearly-Target-Convert-to...
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).
@Andrea_Jess , 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"))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |