Calculate the count of 100% or more in 30, 60 and 90 days

[toovishant]

Hi Team,

I need to create a view, where the calculated column on % by bl_id needs to find how many times it is either equal to or greater than 100% for 30, 60, and 90 days.

Below is the sample test data set

https://docs.google.com/spreadsheets/d/1vbW6c764C44pUayLUgKYUACDq8X-1w11/edit?usp=sharing&ouid=10867...

Required view

Thanks,

Vishant

[v-zhengdxu-msft · Accepted solution]

Hi @toovishant 

I have a certain understanding of your question.

You need to filter the number of entries in the bl_id percentage column by time range (30 days, 60 days, 90 days).

Here is the solution：

Based on the example data you gave, I created a table to store a set date range, such as 30, 60, 90 days

Then created a measure that is used to calculate the number of bars that exceed 100% in 30, 60, 90 days, respectively：

--------------------------------------------------

%by bl_id() =

VAR selectrange = SELECTEDVALUE('Table'[Value])

// Used to extract the required date range from the newly created table

    VAR range_DAY = SWITCH(

        selectrange,

        "%by bl_id(30)", 30,

        "%by bl_id(60)", 60,

        "%by bl_id(90)", 90

    )

// Since selectrange extracts text values, the text is converted to numbers via switch

    VAR current_date = MIN('data'[Date])

// Extract the current value

    VAR end_date = MAXX(

        FILTER(

            ALL('data'),

            'data'[Date] <= current_date + range_DAY

        ),

        'data'[Date]

    )

// Gets the end value of the date

    RETURN

        CALCULATE(

            COUNTROWS('data'),

            'data'[Utilization %] >= 1 && 'data'[Date] >= current_date && 'data'[Date] < end_date

        )

//Obtain the number of entries with a percentage of more than 1 in this date range.

--------------------------------------------------

Once you've set it up, create a matrix with [bl_id] rows and [value] in columns, and [%by bl_id()] in values.

Also use [date] to create a slicer, which can be used to set the current_date, and the result is that you can choose a point in time, and the matrix will show the percentage of values that exceed 1 in the 30th, 60th, and 90th days after that point in time.

By using this method, you can easily set the date range (you can add dates to the 'Table' [value], for example, if you want to filter the number of entries within 120 days, you just need to add "%by bl_id(120)", and add "%by bl_id(120)" to the switch in measure, 120).

Best Regards,

Zhengdong Xu

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

[olgad]

Measure = Calculate(COUNTROWS('dataTable'), 'dataTable'[Utilization %]>=1)

You can see in my case i took the countries instead of classification and for China it counts 5 times and then on the right you can see that indead China has 5 times 100 or more percent. Hope it helps. 

[toovishant]

@olgad  - Thanks for the quick response, i need to show this for a fixed set of 30, 60, and 90 days from the last captured data set date for bl_id.

Thanks,

Vishant

[olgad]

Lets make it a bit more clear and take the folowing bi_id in your data set, it is the 7th of august 2023 that is the last date for this id and then we need to calculate our 30, 60, 90 from there, correct?

[toovishant]

@olgad  - For the given sample data set that's right and when these 30, 60, and 90 days views are created by bl_id this should always have 30, 60, and 90 days of data from the last date.

Thanks,

Vishant

[olgad]

Hi Vishant, what is the logic behind >30, ,>60 days  and are the values the %Utilization from your data?

[toovishant]

Hi @olgad 

That was a manual interpretation of the required view in excel.

Thanks,

Vishant