Fiscal YTD when using MAX

[Axiomite]

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. 

1. BudgetAMT_Max = MAX(factGLTransactions[Budget_Amount]) 

2. BudgetAMTDistinctTotal = SUMX(DISTINCT(factGLTransactions[Account]),[BudgetAMT_Max])

With the last result BudgetAMTDistinctTotal I am able to add all of the column and visualise Description, Actual Amount and Budget_Amount and have the option to use a slicer to choose a month and get the montly Actual Amount and the monthly Budget_Amount. 

However this does not seem to work when I am trying to calculate the YTD Budget_Amount when selecting a month on the slicer. My Dax for YTD as follows: (please note I've created a relationship between TxDate and DimDate[Date] so using the Dimdate table for all date base calculations)

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 )

    )

Adding the Current Budget YTD Fiscal result to the Matrix its no calculating the Total YTD figure based on selected date in slicer. 
Any idea what I am doing wrong? 

Kind regards

[Ashish_Mathur]

Hi,

Not clear about your question.  Is 4600000 the revenue budget of the entire year 2023 of New_value_15?

[Axiomite]

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
 

[Ashish_Mathur]

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.

[Axiomite]

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

[Ashish_Mathur · Accepted 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.

[Axiomite]

@Ashish_Mathur, many thanks for your time in assisting, it works🙏!!

[Ashish_Mathur]

You are welcome.

[lbendlin]

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

[Axiomite]

@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=10166...

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