Need help writing a Dax formula

[Dallas7890]

Hello

I'm unsure how to combine these two amounts into one amount and then show the amount subtracted from the Perfect Monthly availability.

The Perfect Monthly is calculated in Power Query using the #of Days in each month (Jan) 31x 24x60x60 = 2678400

The Imperfect amount is the Total duration column of each Jan record - subtracted from this above Perfect Monthly amount. There are only two dates in January that our system failed. All the other months, the availability is perfect. I would like to be able to reflect this in the report below. I'm not sure how to write the DAX formula. I would like these two records for January to be combined as one total, and then subtracted from the month's January Perfect Availability amount 26784.00. 
The formula for the Monthly Perfect Availability is #of days in a Month x 24x60x60

This is currently how my report looks. It would be nice to show all the Perfect Availability Amounts and the January amounts showing the duration of, 23400 subtracted from the January's Perfect Monthly Availability. 

Thank you kindly

[Anonymous · Accepted solution]

Hi @Dallas7890 ,

Please try below steps:

1. below is my test table

Table:

Table2:

2. add a new column in Table2 with below dax formula

Total Amounts =
VAR cur_month =
    MONTH ( [Month] )
VAR tmp =
    FILTER ( 'Table', MONTH ( [Date] ) = cur_month )
VAR _val =
    SUMX ( tmp, [Duration] )
RETURN
    [Perfect Monthly Avaliability] - _val

3. add a clustered column chart visual with Table2 fields

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Dallas7890 · Accepted solution]

Thank you so much, this is the perfect solution.

[Anonymous · Accepted solution]

Hi @Dallas7890 ,

Please try below steps:

1. below is my test table

Table:

Table2:

2. add a new column in Table2 with below dax formula

Total Amounts =
VAR cur_month =
    MONTH ( [Month] )
VAR tmp =
    FILTER ( 'Table', MONTH ( [Date] ) = cur_month )
VAR _val =
    SUMX ( tmp, [Duration] )
RETURN
    [Perfect Monthly Avaliability] - _val

3. add a clustered column chart visual with Table2 fields

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.