Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anurag_dash
New Member

Code not working

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 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

vyiruanmsft_0-1729585774897.png

 

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

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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

vyiruanmsft_0-1729585774897.png

 

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

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.