Calculated Total per month

[Anonymous]

I am looking to create a report that determines the uptime/downtime of certain outages

i have a diff calculation based on each outage start and finish time which is represented as a column as minutes

what i am trying to achieve without luck is to give me the monthly outage totals based on that diff calculation

my output will be a bar chart showing the outage total for each month across the year

at the same time, will like to get qtrly and yearly breakdown if possible (but important to get the month first and ill possibly group later to determine the other totals)

my total calculation is as per below

Incident Downtime Total =
VAR DateRangeEnd = [Date Range End]
RETURN
    CALCULATE (
        SUM ( 'Work Item Fields'[Incident Downtime in Min] ),
        FILTER ( 'Dates', 'Dates'[Date] = DateRangeEnd )
    )

This will be the accumulated total across all dates, but i cannot seem to filter into a specific month value

[v-shex-msft]

Hi @Anonymous,

I'm not so sure for your formula, can you share some detail contents?

I think "Date Range End" is a measure which used to get the choose in the slicer, "Incident Downtime Total" is a calculated column, right?

If this is a case, you can't create a dynamic calculated column based on slicer, you need to use measure. Slicer works on visual level, it can't effect calculate column/table which based on source data. In summary, calculate column/table has Pretreatmented before selected on slicer, current you can only create a dynamic measure based on slicer.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft

actually the calculated column calculates each downtime event in min as per this formula

Incident Downtime in Min = IF(ISBLANK('Work Item Fields'[Outage End]),BLANK(), (24. * 60 *([Outage End]-[Outage Start])))

then i use a measure as per my first post to get the total, but i cannot seem to get the specific value for each month

what happens is my April month might have a total of 500 min downtime

then the next month May has 100 min downtime

my graph or value card should have something like this

April = 500

May = 100

but my bar chart will appear is

April = 500

May = 600 (where its 500+100)

[v-shex-msft]

Hi @Anonymous,

If you want to calculate monthly rolling total , you can try to add a condition to check if the dates are in same month:

Date are in same year:

cident Downtime Total =
VAR DateRangeEnd = [Date Range End]
RETURN
    CALCULATE (
        SUM ( 'Work Item Fields'[Incident Downtime in Min] ),
        FILTER ( 'Dates', 'Dates'[Date] = DateRangeEnd ),
        Month('Dates'[Date])=Month(EARLIER('Dates'[Date]))
)

Date column contains different years:

cident Downtime Total =
VAR DateRangeEnd = [Date Range End]
RETURN
    CALCULATE (
        SUM ( 'Work Item Fields'[Incident Downtime in Min] ),
        FILTER ( 'Dates', 'Dates'[Date] = DateRangeEnd ),
        FORMAT('Dates'[Date],"mm/yyyy")=FORMAT(EARLIER('Dates'[Date]),"mm/yyyy")
    )

Regards,

Xiaoxin Sheng

[Anonymous]

hi @v-shex-msft

i get the following error

"Earlier/Earliest refers to an earlier row context which doesnt exist"

any help?

[v-shex-msft]

Hi @Anonymous,

Can you share some detail info?

Regards,

Xiaoxin Sheng

[Anonymous]

 not sure what detail you require

i put in the formula/s you provided and get the same error each time regarding the EARLIER/EARLIEST function

thanks

[v-shex-msft]

Hi @Anonymous,

If you can, please share a sample pbix file, I will test on it.(you can upload it to onedrive or other drive, then share the link here)

Regards,

Xiaoxin Sheng