Bar chart with no sequential data

Power BI
1

Hi all,

I need your help to overcome this task.
I really don’t know how to do it.

image
image1105×401 26.2 KB
Sample.pbix (455.8 KB)

Thanks

Regards
JoaoMonteiro

2

Hi @JoaoMonteiro

There are no data in your PBIX file, look like DirectQuery mode

image_2021-02-23_095015
image_2021-02-23_0950151338×713 97.7 KB

3

Hi @jbressan

I think now its OK
Could you check, please.

Sample.pbix (440.4 KB)

Thanks

4

Hi @JoaoMonteiro

I not know sure but I understand you want to calculate Total Active Employees, I’m hope this measure will be useful for you!!

Total No of Active Employees = 
VAR CurrentDate =
    MAX ( 'Date'[Date] )
VAR ActiveEmployees =
    CALCULATE (
        COUNTROWS ( RpgResources ),
        ALL ( 'Date' ),
        'Date'[Date] <= CurrentDate,
        ISBLANK ( RpgResources[ExitDate] )
            || RpgResources[ExitDate] >= CurrentDate
    )
VAR Result =
    IF ( YEAR ( CurrentDate ) <= YEAR ( TODAY () ), ActiveEmployees )
RETURN
    Result

image_2021-02-23_112015
image_2021-02-23_1120151000×371 36.2 KB

Sample - JoseBressan.pbix (443.5 KB)

5

Hi @jbressan

Unfortunately I didn’t explain myself well and the solution you presented me is not what I need.
Imagine that I want the headcount for January 2018.
The calculation for this head count is all rows that fulfill the requirements:

  • Start Date less than 1 January 2018 and End Date greater than 31 January

On the image I have a better explanation.

image
image1109×404 37.6 KB

6

Hi @JoaoMonteiro

I have made below changes in your sample. Please check if this is what you need.

  1. Marked your Date table as Date Table

  2. Created below measure…

    HeadCount =
    VAR MinDate = MIN ( ‘Date’[Date] )
    VAR MaxDate = MAX ( ‘Date’[Date] )
    RETURN
    CALCULATE ( COUNTROWS ( RpgResources ),
    ALL ( ‘Date’ ),
    RpgResources[EntryDate] < MinDate,RpgResources[ExitDate] > MaxDate)

Final Solution File EDNA_Solution_HC.pbix (423.5 KB)

7

Hi @JoaoMonteiro, did the response provided by @ankit help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

8

Hi @JoaoMonteiro, we’ve noticed that no response has been received from you since the 25th of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

9

Hi @ankit

I appreciate your cooperation, but the solution you present is not what I am looking for.
Notice the image below.
What I want is for the chart to show only certain months according to the selection made in the Year.
For example, if you select the year 2021 and we are in March, I want you to present Feb, Jan 2021 and Dec and Jan 2020; but if the user selects 2020, I just want to display the values for Jan and Dec for the selected year.

image

Thanks

11

Hi @JoaoMonteiro

In the initial file “Sample.PBIX” file shared there was Month & Year slicer and not any Year slicer. Solution was shared based on that.

Now, as per new requirement do let me know on what basis are you showing only specific months for different selection and how are you getting 145/451 values for all in the example provided ?

Thanks
Ankit J

12

Hi @JoaoMonteiro, we’ve noticed that no response has been received from you since the 3rd of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

13

Hi @JoaoMonteiro, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!