agregate data by month

[leandro_vegacr]

Hello everyone.

I got this data https://drive.google.com/file/d/1qh0RzWkw3m3IN6divB-1f2QNU4RkSeXY/view?usp=drivesdk__;!!ETL5SZvLnA!-... as an example of the data I'm working with.

With this data, we need to perform some analysis, we have been able to do some already, but there are some requirements we are not sure how to do.

We need to display in a table, given this:

- number of distinct loops that have an uptime greater than 10 for ech month (named measured_loops) (done)

- number of distinct loops that have an uptime greater than 10 and a PTIN greater than 90. Then, as another measure, somethign very simiar but with PTIN greater than 95 (named normal_loops) (done)

- Monthly percent of normal loops with regard to measured loops (named monthly avg) (done)

- Monthly avg of normal loops with regard to measured loops, but taking into account what goes on daily, not monthly as a whole. This is, go each day and check what normal loops divided by measured loops is, summarize those results and divide that sum by the number of days for that month. If measured loops is 0, return 0 as the result of the division.

- Same as before, but excluding the days when measured loops equals 0. So, for example, if in September just 3 days had at least 1 measured loop, divide the sum of the division by 3, not by 30.

We are displaying this in a table that has both months and days so the measures are being calculated for ech period of time just fine. However, we are struggling with understanding how these last 2 should be written as measures. here is the current table, just for reference: 

Thanks for your help.

pd: I did a very similar question before, which v-nuoc-msft was very kind in answering, but requirements here are a little different and actual data is provided. Did not know if it was correct to post again; if it is considered impolite, my excuses. 

[lbendlin · Accepted solution]

what is a "normal loop"  ?

See attached for a general setup.  Your data model must have a Calendar table.

[leandro_vegacr]

Hello lbendlin, thanks for the information. I have a calendar table, yes. I just shared the factual data, but we are using it. A "normal loop" is a loop with both an uptime greater than 10 and a PTIN greater than 90 or greater than 95. It's the result of the second measure. Will take a look at your pbix, thanks!