Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
zaza1098
Frequent Visitor

Prediction of availabilities

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 periodsbjhj.PNGCapture.PNG

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

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

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.

View solution in original post

10 REPLIES 10
zaza1098
Frequent Visitor

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

Icey
Community Support
Community Support

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
    )
)

Icey_0-1652084756921.png

 

 

Best Regards,

Icey

 

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

zaza1098
Frequent Visitor

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?

Icey
Community Support
Community Support

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.

zaza1098
Frequent Visitor

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

lbendlin
Super User
Super User

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.

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

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

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

Icey
Community Support
Community Support

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] )
    )
)

Icey_0-1652078808660.png

 

 

Best Regards,

Icey

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.