topic Re: Code not working in DAX Commands and Tips

Code not working

Anurag_dash — Sun, 20 Oct 2024 15:42:51 GMT

There requirement is to calculate Avg Alram per day

Numerator = Total the COA Monthly by

Denominator = Todays Date of the day in the month selected

Exp : For current year

Numerator = 100

Denominator = Todays date 20 oct means... 20

Exp : For past year or Historical years like for Oct

Numerator = 100

Denominator = No of days in that month like for Oct will be 31

The code that I have written not working properly... Can any one help to get the correct results...

Code is ..

Average Alarms Per Day (MTD) =

VAR _Date = SELECTEDVALUE('Calendar'[Month-Year])

VAR _MonthStart = DATEVALUE(DATE(YEAR(_Date), MONTH(_Date), 1))

VAR _CurrentYearStart = DATEVALUE(DATE(YEAR(TODAY()), 1, 1))

VAR _CurrentDate = TODAY()

VAR DaysInMonth = DAY(EOMONTH(_MonthStart, 0))

VAR TotalAlarms = CALCULATE(

[COA Monthly],

'Calendar'[Date] >= _MonthStart && 'Calendar'[Date] <= IF(_Date = _CurrentDate && _CurrentYearStart = _Date, _CurrentDate, MAX('Calendar'[Date])))

VAR DaysToCalculate = IF(_Date = _CurrentDate && _CurrentYearStart = _Date, DAY(_CurrentDate), DaysInMonth)

VAR AverageAlarmsPerDay = DIVIDE(TotalAlarms, DaysToCalculate)

RETURN AverageAlarmsPerDay

Please help me with correct code

Re: Code not working

lbendlin — Sun, 20 Oct 2024 20:31:14 GMT

Please provide more details - what does "not working" mean? what does "correct" mean?

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.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.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Code not working

Anonymous — Tue, 22 Oct 2024 08:30:25 GMT

Hi Anurag_dash ,

Please update the formula of measure as below and check if it can return the expected result.

Average Alarms Per Day (MTD) = VAR _Date = SELECTEDVALUE ( 'Calendar'[Date] ) VAR _MonthStart = DATEVALUE ( DATE ( YEAR ( _Date ), MONTH ( _Date ), 1 ) ) VAR _Selyear = YEAR ( _Date ) VAR _Selmonth = MONTH ( _Date ) VAR _CurrentDate = TODAY () VAR _Curyear = YEAR ( _CurrentDate ) VAR _Curmonth = MONTH ( _CurrentDate ) VAR DaysToCalculate = SWITCH ( TRUE (), _Selyear < _Curyear, DAY ( EOMONTH ( _MonthStart, 0 ) ), _Selyear = _Curyear && _Date <= _CurrentDate, IF ( _Selmonth < _Curmonth, DAY ( EOMONTH ( _MonthStart, 0 ) ), DAY ( _CurrentDate ) ) ) VAR TotalAlarms = CALCULATE ( [COA Monthly], 'Calendar'[Date] >= _MonthStart && 'Calendar'[Date] <= _Date ) VAR AverageAlarmsPerDay = DIVIDE ( TotalAlarms, DaysToCalculate ) RETURN AverageAlarmsPerDay

If the above one can't help you figure out, please provide some raw data in your tables(exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards