Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
khan8222
New Member

combining actual and budget data based on date filter

Hi, 

 

I am trying to create a forecast but want it to be dynamic, i.e. if i select a date range then it should calculate the actuals upto the max selected date and then add budget values for the remaning periods. 

The fiscal year i am working with goes from Aug to July so i have data for August 2022 to July 2023.  

 

I have a date table as well but i cant figure out how to have the dax know when to start and when to end. 

 

for e.g if i have date filter on for 08/01/2022 - 04/30/23, then the dax should calculate forecast by combining actuals from 08/01/2022 - 04/30/2023 with budget data for 05/01/23 - 07/31/2023

 

I tried using the following formula but it works only when my fiscal period is calander year based ie January to December. 

i have a measure that sums all budget data [TotalBudIncome] and another measure that sums all actual income data [TotalActIncome]

 

 

I am trying to create a forecast but want it to be dynamic, i.e. if i select a date range then it should calculate the actuals upto the max selected date and then add budget values for the remaning periods. 

The fiscal year i am working with goes from Aug to July so i have data for August 2022 to July 2023.  

 

I have a date table as well but i cant figure out how to have the dax know when to start and when to end. 

 

for e.g if i have date filter on for 08/01/2022 - 04/30/23, then the dax should calculate forecast by combining actuals from 08/01/2022 - 04/30/2023 with budget data for 05/01/23 - 07/31/2023

 

to clarify, the above formula was using a paremeter value that can only go from 1 - 12 as i could only generate a seried for 1 to 12. also the data i have ranges from Feb 2021 to Dec 2023

 

ResiIncomeRFX =

IF(
MONTH(SELECTEDVALUE('Date'[Date])) <= Parameter[Parameter Value],
SUMX(
FILTER(
'Date',
MONTH('Date'[Date]) <= Parameter[Parameter Value]
),
[TotalActIncome]
),
SUMX(
FILTER(
'Date',
MONTH('Date'[Date]) > Parameter[Parameter Value]
),
[TotalBudIncome]
)
)
+
IF(
MONTH(SELECTEDVALUE('Date'[Date])) <= Parameter[Parameter Value],
SUMX(
FILTER(
'Date',
MONTH('Date'[Date]) > Parameter[Parameter Value]
),
[TotalBudIncome]
),
SUMX(
FILTER(
'Date',
MONTH('Date'[Date]) <= Parameter[Parameter Value])
,[TotalActIncome]
)
)

 

data sample 

 

TranTypeAmountDateAccountID
BUDGET5

07/01/2023

108
Actual3001/15/2023108

 

6 REPLIES 6
lbendlin
Super User
Super User

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Please see sample data below. Hope this gives more backfground. 

 

AccountIDAmountBranchIDBRSUBFinPeriodIDModuleRPFinPeriodStartDateSubIDTranType
15.001766176613202310BUDGET20231005/01/202313BUDGET
11520120124202202GL20220209/01/202124Actual
151766176613202311BUDGET20231106/01/202313Forecast

 

 

The below show forecast for given account based on a cutoff date of 04/30/2023. The dax right now calculates actuals upto April 2023 then adds FOrecast figures for May - July to provide an annual forecast number. 

 

RIght now i have hard coded the dates but i wan the dax to be able to calculate based on the cutoff date the user selects.

 

2023-06-09_15-23-51.png

This sample data is not usable. Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

 i supplied the table that has the data thats required for the dax calculation. I cannot provide a pbx file as it has sensitive data. The above data is as per the link you attached. 

if you cannot provide usable sample data then we have a hard time helping. 

ok, you dont have to maybe someone else will. Thanks 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.