combining actual and budget data based on date filter

[khan8222]

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 

TranType	Amount	Date	AccountID
BUDGET	5	07/01/2023	108
Actual	30	01/15/2023	108

[lbendlin]

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...

[khan8222]

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

AccountID	Amount	BranchID	BRSUB	FinPeriodID	Module	RPFinPeriod	StartDate	SubID	TranType
1	5.00	1766	176613	202310	BUDGET	202310	05/01/2023	13	BUDGET
1	15	201	20124	202202	GL	202202	09/01/2021	24	Actual
1	5	1766	176613	202311	BUDGET	202311	06/01/2023	13	Forecast

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.

[lbendlin]

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.

[khan8222]

 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. 

[lbendlin]

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

[khan8222]

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