topic Re: combining actual and budget data based on date filter in DAX Commands and Tips

combining actual and budget data based on date filter

khan8222 — Thu, 08 Jun 2023 17:41:32 GMT

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

Re: combining actual and budget data based on date filter

lbendlin — Fri, 09 Jun 2023 20:58:35 GMT

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-Forum/ba-p/963216
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/1447523

Re: combining actual and budget data based on date filter

khan8222 — Fri, 09 Jun 2023 22:29:17 GMT

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.

Re: combining actual and budget data based on date filter

lbendlin — Fri, 09 Jun 2023 22:37:08 GMT

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.

Re: combining actual and budget data based on date filter

khan8222 — Fri, 09 Jun 2023 22:46:56 GMT

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.

Re: combining actual and budget data based on date filter

lbendlin — Fri, 09 Jun 2023 23:22:24 GMT

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

Re: combining actual and budget data based on date filter

khan8222 — Fri, 09 Jun 2023 23:28:38 GMT

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