Reporting open records by month

[Anonymous]

Hi all

I need some help to show the number of open problem records every month.  Ideally, I would like to show the count on the line series on a combo chart.

I've tried a few things but they just don't seem to work.

An open record is a reference in a status of open or open with a workaround.  

The data has the created date, closed date, status, last update and reference number. 

sample pbix file

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

here is a same post for you refer to:

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

and you may refer to this formula:

Open = 
VAR tmpCalendar = ADDCOLUMNS('DateTable',"Month",MONTH([Date]),"MonthYear",VALUE(YEAR([Date]) & FORMAT(MONTH([Date]),"0#")))
VAR tmpBilling = ADDCOLUMNS('Problem',"MonthYearBegin",VALUE(YEAR([Created]) & FORMAT(MONTH([Created]),"0#")),
                                      "MonthYearEnd",IF([Closed]=BLANK(),VALUE(YEAR(TODAY()) & FORMAT(MONTH(TODAY()),"0#")),VALUE(YEAR([Closed]) & FORMAT(MONTH([Closed]),"0#"))))
VAR tmpTable = 

    FILTER(
        GENERATE(
            tmpBilling,
            SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear])
        ),
        [MonthYear] >= [MonthYearBegin] &&
        [MonthYear] <= [MonthYearEnd]
    ) 
    
 
RETURN COUNTAX(FILTER(tmpTable,[Status]="Open with Workaround"||[Status]="Open"),[Status])

They have the same logic, if you still have problem, please share the expected output based on these sample data.

Best Regards,

Lin