how to get context boundaries

[Anonymous]

Hello everyone!

How to get date borders by which data is grouped in context?
I have a date type field, and two groups (by month and week). The report has a drilldown set up: I can see data by month or week.
I want to calculate the SLA value depending on the number of days that fall into context.
How to do it?

In other words, I want to visualize a percentage, based on time amount depending on choosen period

I appreciate any help!

[amitchandak]

@Anonymous , Not ver clear, you can use distinctcount(date[date])

or datediff(min(date[date]),max(date[date]),Day)

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Anonymous]

I thought about this.

But it doesn't work if I have insufficient data.

For example, the week lasts from 1st to 7th.

I have only 2 incidents, closed within choosen week, one of them was closed on 2nd, the second - on 6th.

So, DATEDIFF(...) or COUNTROWS(CALENDAR(...)) will give wront result: 5 days.

I need 7.

[amitchandak]

@Anonymous , I think date diff should give you 7 take that from date table and calculate it in a measure first and then use 

[Anonymous]

@amitchandak , maybe something wrong with my data fields...

I've just tried

AV_test = datediff(min(incident_sla[taskslatable_start_time]); max(incident_sla[taskslatable_start_time]);DAY)

The result is unexpected:

[v-lionel-msft]

Hi @Anonymous ,

Like this?

Days per week = 
CALCULATE(
    COUNT('Date'[Week]),
    ALLEXCEPT('Date', 'Date'[Week])
)

I know you want to calculate the number of days, but I am not sure what the conditions are, the number of days per week or the number of days per month?

Best regards,
Lionel Chen

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

[Anonymous]

I want to get the bin size in days ofany group I use in report: taskslatable_start_time (months) or taskslatable_start_time (weeks).

When I use taskslatable_start_time (months) - the number of day in calendar month. And 7 - for taskslatable_start_time (weeks).

[v-lionel-msft]

Hi @Anonymous ,

Has your problem been solved?

Best regards,
Lionel Chen

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

[v-lionel-msft]

Hi @Anonymous ,

You might be able to do like this.

Days by week = 
CALCULATE(
    COUNTROWS( Date ),
    ALLEXCEPT( Date, 'Date'[taskslatable_start_time (weeks)])
)

Days by month = 
CALCULATE(
    COUNTROWS( Date ),
    ALLEXCEPT( Date, 'Date'[taskslatable_start_time (months)])
)

Best regards,
Lionel Chen

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

[Anonymous]

Hello!

How can I use this construction in the one single measure I use in report whenever I group the data by months or weeks?

Measure = <Downtime> / <Days in context> * 24 * 60

So far, I see the one solution: to do 2 separate measures: 1st - for using with months and 2nd - weeks. And 2 reports, with no drill-down.

[v-lionel-msft]

Hi @Anonymous ,

"So far, I see the one solution: to do 2 separate measures: 1st - for using with months and 2nd - weeks. And 2 reports, with no drill-down."

Well, this seems to be a good way.

Best regards,
Lionel Chen

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

[Anonymous]

Ok, I will check DATEDIFF and write about results! Thank you!

I've tried COUNTROWS(CALCULATE()) earlier, but it did not work.