Sum of Data as it was at a certain date

[Anonymous]

I need to report on 'Open Faults' and the number of days they've been open at the end of each month. 

e.g.

DateCreated	Fault ID	Fault Status	August 2021	July 2021	June 2021	May 2021	April 2021	March 2021
05/08/2020 00:00	1	Open	390	359	328	298	267	237
24/11/2020 00:00	2	Open	356	325	294	264	233	203
Total			746	684	622	562	500	440

(The month columns are how long the faults have been  open on the final day of the month)

Does anyone know of a DAX expression that can be used to replicate the outcome of this table, rather than me having to create a translation layer/table to do the calculation for me?

I'd like to be able to display it along a time axis (DateCreated) but it seems impossible!

[BA_Pete]

Hi @Anonymous ,

Try the measure below. It relies on a disconnected calendar table to populate the column headers and create the measure:

_daysOpen = 
VAR __dateToCheck = MAX(calendar[date])
VAR __dateCreated = MAX(yourTable[dateCreated])
RETURN
COUNTROWS(
    FILTER(
        ALLEXCEPT(calendar, calendar[monthYear]), //calendar[monthYear] = column header field used
        calendar[date] > __dateCreated
        && calendar[date] <= __dateToCheck
    )
)

This gives me the following output in a matrix visual:

You can tweak the '>' and '<=' operators within the FILTER function to fit with your exact reporting needs.

You may also want to wrap the whole RETURN section in an IF statement to only evaluate when [faultStatus] = "Open", but I'll leave that to your taste.

Pete

[v-chenwuz-msft]

Hi Nialloe19,

Can you give some examples? Or what kind of table or graph you want, please give some example data, thanks.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Anonymous Not sure, but I'm thinking you might want: 
Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365