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