topic Prediction of availabilities in Report Server

Prediction of availabilities

zaza1098 — Mon, 02 May 2022 15:34:48 GMT

Hello. I need help.
I have a table that provides information on the availability of a company's employees. This table gives their future availabilities as well. And I want one, based on the future availabilities I have, with a curve that tells me for every two weeks how many available employees I have. I don't know how to count according to dates and have future dates on the axis, at intervals of two weeks.

This involves gathering the number of availabilities over two-week periods

Here, we choose only to represent the availabilities to come and on periods of two weeks

Re: Prediction of availabilities

lbendlin — Thu, 05 May 2022 00:06:49 GMT

In your calendar table add another column that identifies your week buckets. Use that for the X axis. You may also want to try using a column chart type rather than a line chart.

Re: Prediction of availabilities

zaza1098 — Fri, 06 May 2022 13:04:39 GMT

Hello. I finally managed to do a crossJoin between two of my tables and got future dates as I wanted.
My problem currently is that I have to count the availabilities of the previous week, and add it to that of the current week.
Example:
Week 1 =3 availabilities.
week 2 =5 availabilities.
week 3 =9 availabilities.
week4 = 5 availabilities.
I want to have a measure that tells me that at week 1 I have 3 available,
at week 2 I have 8 available,
at week 3 I have 8+ 9 therefore 17 available,
at week 4 I have 17 +5 so 12 available.

The objective is to count the availability of the previous week and add them to the week in question and I'm really lost. I'm working on this since the beginning of the week.
I woud really appreciate your help. Thank you very much

Re: Prediction of availabilities

lbendlin — Fri, 06 May 2022 13:46:03 GMT

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Prediction of availabilities

zaza1098 — Fri, 06 May 2022 14:03:08 GMT

Okay.
I tried to insert the file.pbix but it was not possible. so I put it in a drive with the link
https://drive.google.com/file/d/1xpGRg-xJKOstArPIY7ogskVPT3klGLxJ/view?usp=sharing

I'm supposed to count the "available = yes" of the previous week and assign it to the next week

Re: Prediction of availabilities

Icey — Mon, 09 May 2022 06:47:14 GMT

Hi zaza1098 ,

Please check if this is what you want:

Accumulative Count = CALCULATE ( COUNT ( data[available] ), FILTER ( ALLSELECTED ( data ), data[available] = "yes" && data[WeeknumdateDay] <= MAX ( data[WeeknumdateDay] ) ) )

Best Regards,

Icey

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

Re: Prediction of availabilities

zaza1098 — Mon, 09 May 2022 08:17:03 GMT

Hi Icey and Hi lbendlin , Thank your for your answer because I don't know how to do it.

I looked at your solution, and I think I hadn't completely clarified my problem because my English is not very good.
I would actually like for example, this week, to be told how many people I had available last week. And that next week, I'm told I had how many available I have today. For the current week, we don't consider its availabilities.
This means that at week 5, there will be no values because there was nothing at the previous week, i.e. week 4. But this is still cumulative, in the sense that : At week 15, I will have all those available from week 5 until week 15-1, so 14

Finally, for this job, I have to do another version, thinking to myself, for the current week, how much was available 3 weeks ago. Small example (in week 15, how many availabilities were there in week 12)

I will be grateful to find a solution. If you have tutorials for me to teach perfectly Powerbi, I would appreciate.
Thank you very much

Re: Prediction of availabilities

Icey — Mon, 09 May 2022 08:29:54 GMT

Hi zaza1098 ,

Something like this?

Accumulative Count except This week = CALCULATE ( COUNT ( data[available] ), FILTER ( ALLSELECTED ( data ), data[available] = "yes" && data[WeeknumdateDay] < MAX ( data[WeeknumdateDay] ) ) )

Accumulative Count last 3 weeks = CALCULATE ( COUNT ( data[available] ), FILTER ( ALLSELECTED ( data ), data[available] = "yes" && data[WeeknumdateDay] < MAX ( data[WeeknumdateDay] ) && data[WeeknumdateDay] >= MAX ( data[WeeknumdateDay] ) - 3 ) )

Best Regards,

Icey

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

Re: Prediction of availabilities

zaza1098 — Mon, 09 May 2022 08:48:10 GMT

Hi Icey ,
I agree with the first proposition. I think that's what I wanted. But for the second case, since it collects data for the previous three weeks each time. This means that for this case, we will only have results at week 8 (which corresponds to the sum of the provisions of 5, 6 and 7)

Also just for my own understanding, if I wanted to assign the values from the previous week to the current week, without a cumulative sum, what should I change?

Re: Prediction of availabilities

Icey — Tue, 10 May 2022 01:36:06 GMT

Hi zaza1098 ,

//This means that for this case, we will only have results at week 8 (which corresponds to the sum of the provisions of 5, 6 and 7)

For week 6 and 7, there are also corresponding results.

week 6 = week 5

week 7 = week 5 + week 6

week 8 = week 5+ week 6 + week 7

//Also just for my own understanding, if I wanted to assign the values from the previous week to the current week, without a cumulative sum, what should I change?

If you just want to get the previous week's result, try this:

Accumulative Count Previous Week = CALCULATE ( COUNT ( data[available] ), FILTER ( ALLSELECTED ( data ), data[available] = "yes" && data[WeeknumdateDay] = MAX ( data[WeeknumdateDay] ) - 1 ) )

Best Regards,

Icey

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

Re: Prediction of availabilities

zaza1098 — Tue, 10 May 2022 07:06:13 GMT

Accumulative Count Previous Week = CALCULATE ( COUNT ( data[available] ), FILTER ( ALLSELECTED ( data ), data[available] = "yes" && data[WeeknumdateDay] <= MAX ( data[WeeknumdateDay] ) - 1 ) )

Because without the "<", there is no the cumulative count for the first case of your answer.
I thank you very much, now it is clear 😀
Thankkk you