Calculated Formula to count records within own month

[GunnerJ]

I'm needing to show how many service orders were opened in a given month and how many service orders were closed in a given month. The code below is my best attempt so far but not giving me my expected results for the opened portion. I first make my date variable and then attempt to count how many records have the same month and year. Is there anything obvious that I'm missing?

My end result would allow me to make a table showing something like "June 2019, 100 Service Orders Opened", "July 2019, 121 Service Orders Opened"

Service Orders Opened =

VAR MonthInRow =DATE(YEAR(Workflow[BI_OPEN_DT]),MONTH(Workflow[BI_OPEN_DT]),DAY(Workflow[BI_OPEN_DT]))

return

COUNTROWS(FILTER(ALL(Workflow),MONTH(Workflow[BI_OPEN_DT]) = MONTH(MonthInRow) && YEAR(Workflow[BI_OPEN_DT]) = YEAR(MonthInRow)))

[Anonymous]

Please post a screensshot of your data. I am lost trying to read that code not understanding how any of the columns look.

[GunnerJ]

There's more data than just this but these are the fields that really matter for this scenario. Each Service Order Number has an open and close date field. In this example all Open Dates are in January of 2019 and I'd like to show how many SO's were opened in that month. The counter would be showing how many SO's were closed in Jan 2019 which in this screen shot is most but not all of the records. When looking for closed records some of these would count towards FEB 2019.

[Ashish_Mathur · Accepted solution]

Hi,

Try this

1. Create a Calendar Table and build the following relationships:
   • From BI_Open_Dt coulmn to the Date column of the Calendar Table - Active
   • From BI_Close_Dt column to the Date column of the Calendar Table - Not active
2. In your Calendar Table, extract Yer and Month with the following calculated column formulas: Year = Year(Calendar[Date]) and Month = FORMAT(Calendar[Date],"mmmm")
3. To your visual, drag Year and Month from the Calendar Table
4. Write these measures
   1. Opened = DISTINCTCOUNT(Data[BI_SO_NBR])
   2. Closed = CALCULATE([Opened],USERELATIONSHIP(Data[BI_Close_Dt],Calendar[Date]))

Hope this helps.

[GunnerJ]

@Ashish_Mathur  Thank you that got me what I needed!

If you have the time could you maybe explain the dax a bit? I'm newer to Dax and Powerbi and would love to more deeply understand the solution for further application. When I look at the Open measure it seems to just be counting all Service Orders. What makes it only look at those with the same open date month? Also what is the significance in choosing the specific active and inactive relationship?

Thank you again!

[Ashish_Mathur]

You are welcome.  Since the active relationship is from the Open date column, the COUNTORWS function will only count the records which fall in that month (Month is from the Calendar Table).  For the close date column, that specific relationship has to be activated before counting the rows.

[GunnerJ]

@Ashish_Mathur  Hello Ashish,

I had a quick question refering to the solution you previously provided. I'm displaying my values in treemap visuals seperately and the Closed values appear to just be mimicing the opened values. The picture below has both treemaps that are looking at May 2019. The Opened visual shows the correct value of 240 for "DISC" but Closed doesn't show 249 but instead seems to repeat the first visual. The only difference is that in the "values" section I've placed Opened and Closed fields you'd previously helped with. Do you have any idea as to why closed isn't showing the correct values? Thanks

[Ashish_Mathur]

I will need to see your file.  Share the link from where i can download your PBI file.