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
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
data sample
TranType | Amount | Date | AccountID |
BUDGET | 5 | 07/01/2023 | 108 |
Actual | 30 | 01/15/2023 | 108 |
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.
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.
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
15 | |
14 | |
13 | |
9 |
User | Count |
---|---|
60 | |
23 | |
22 | |
19 | |
13 |