Forum Discussion
Fiscal YTD when using MAX
- 3 years ago
Hi,
Revise the measure to:
Total BudgetAMT_Distinct = SUMX(GENERATE(DISTINCT(SampleData[Master_Sub_Account]),DISTINCT(dimDates[Month])),[Total BudgetAMT_Max])Hope this helps.
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-p/963216
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
- Axiomite3 years ago
Resolver II
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 sectionIf 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