Code not working

[Anurag_dash]

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 

[Anonymous · Accepted solution]

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

[lbendlin]

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

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/1447...