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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.