cancel
Showing results 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

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

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

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

New Member

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.

Super User

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.

New Member

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.

Super User

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

New Member

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors