Fiscal YTD when using MAX

lbendlin ,
I've uploaded sample data into excel, please confirm if you need it to be in Power BI then I can do so.

https://docs.google.com/spreadsheets/d/1n3mlaRA-1vnI3McwZbTkR39vNBcnxhZ-/edit?usp=sharing&ouid=101669885059815625932&rtpof=true&sd=true

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


 

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