Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Good day,
Current with a table containing various columns but primarly a TxDate,Description, Acutal Amount, Accounts, Budget_Amount. The Actual Amount is on the Description level while the Budget amount is on the accounts level.
I've managed to calculate the Budget Amount per month regardless of the granularity by using the following dax.
Solved! Go to Solution.
Hi,
Revise the measure to:
Total BudgetAMT_Distinct = SUMX(GENERATE(DISTINCT(SampleData[Master_Sub_Account]),DISTINCT(dimDates[Month])),[Total BudgetAMT_Max])
Hope this helps.
Hi,
Not clear about your question. Is 4600000 the revenue budget of the entire year 2023 of New_value_15?
Hi @Ashish_Mathur ,
Sample figures are only for the Month of March 2023 to simplify matters. The Revenue budget should provide a result that equals 269,842,477.30, for the Month of March 2023
I do achieve this result using:
BudgetAMT_Max = MAX(factGLTransactions[Budget_Amount]) BudgetAMTDistinctTotal = SUMX(DISTINCT(factGLTransactions[Account]),[BudgetAMT_Max])
However not sure if Im just lucky and might run into problems.
Thus lets assume data is entered for Budget and the result is
269,842,477.30 for March
269,842,477.30 for April
500,000,000.00 for June
The Revenue YTD budget figure should be the sum of the Revenue total monhtly budget figure iow a Revenue Budget YTD figure of 1,039,684,954.60
However, the data provided with a column MasterSub Account that contains New_values from New_value_ to New_value_15. You will see that every New_value in the MasterSub Account contains a budget figure but duplicated for every txdate. It is also a different granulatiry if you look at the Revenue Amount numbers (hence the NULL values)
Hope the above clarifies?
Kind Regards
I am still not clear. For me to be able to help you, share sample data (which can be pasted in an MS Excel file) and show the expected result in a Table format very clearly.
@Ashish_Mathur Let me know if the sample pbix file gives a better explanation. Link below.
https://drive.google.com/file/d/14IDcN7WeWHxzxA15Mmt5KaBLUUFAOEBU/view?usp=sharing
Thank you for your time.
Hi,
Revise the measure to:
Total BudgetAMT_Distinct = SUMX(GENERATE(DISTINCT(SampleData[Master_Sub_Account]),DISTINCT(dimDates[Month])),[Total BudgetAMT_Max])
Hope this helps.
You are welcome.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
@lbendlin ,
I've uploaded sample data into excel, please confirm if you need it to be in Power BI then I can do so.
1. I would like to see if my DAXis correct for getting the Total Budget Amount, thus far it has given me the correct result per month when using a date slicer, it also rolls up to the 'section' columns level so I can compare for Example Revenue with Amount and Buget for example. (You will notice the difference in granularity between column section and Budgetamount)
DAX
BudgetAMT_Max = MAX(factGLTransactions[Budget_Amount])
BudgetAMTDistinctTotal = SUMX(DISTINCT(factGLTransactions[Account]),[BudgetAMT_Max])
This will will be similar as in TSSQL to get the correct Budget Amount
SELECT
section,
SUM(DISTINCT BudgetAmt) AS TotalBudget
FROM Table
WHERE TxDate BETWEEN '2023-03-01' AND '2023-03-31' AND section = 'Revenue'
GROUP BY section
If all the above is correct the I would like to create YTD Budget Amounts like I would normall do using the following.
Current Budget YTD Fiscal =
VAR YearStartMonth = 3
VAR YearStartDay = 1
VAR MaxDate =
MAX (dimDates[Date])
VAR MaxYear =
YEAR ( MaxDate )
VAR YearStartDateThisYear =
DATE ( MaxYear, YearStartMonth, YearStartDay )
VAR YearStartDateLastYear =
DATE ( MaxYear - 1, YearStartMonth, YearStartDay )
VAR YearStartDateSelected =
IF (
YearStartDateThisYear <= MaxDate,
YearStartDateThisYear,
YearStartDateLastYear
)
RETURN
CALCULATE (
[BudgetAMTDistinctTotal],
DATESBETWEEN ( dimDates[Date], YearStartDateSelected, MaxDate )
)
Currenly it is not giving the correct result.
Please let me know if you require any additional information, alternatively I will revert back to SQL maniplulate the data .
Kind regards
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
91 | |
89 | |
79 | |
69 | |
68 |
User | Count |
---|---|
222 | |
128 | |
117 | |
82 | |
77 |