Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Reporting open records by month

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

 

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

1 Reply

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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