How to get 3months consecutive Low Worktime Users!

[sagarsahoo_123]

Hi Team,

Hope all are doing good. Need your help to resolve a query. I have a table having Month, Personal_ID, worktime columns. Based on month selection i want to bring last 3 months userid who have recorded worktime less than 6 hours. In this regard i have done and plotted into matrix table, but only thing i need to eliminate the Blank cell value.

In the above December 2024 month is selected, hence it displayed last 3 months Oct to Dec 24 personal id having Worktime less than 6 hours, but i want those users who are having value for all 3months less than 6 hours. I want to exclude those blank cell values Could you please .help to resolve this!

Below given the DAX code which i am using to get this.

-----

Last_3M_WT = var max_date=max(FY_Calendar[Date])

            Var PreviousDates =DATESINPERIOD('6M_Calendar'[Date],max_date,-3,Month)

            Var Result = calculate(VW_ARC_SKILL[Avg WT],

                        REMOVEFILTERS('FY_Calendar'),

                        KEEPFILTERS(PreviousDates),

                        USERELATIONSHIP(FY_Calendar[Date],'6M_Calendar'[Date]))

            Return result

----------

Avg WT = AVERAGE(VW_ARC_SKILL[WorkTime])

--------

Can you please help me on this! Any help will be highly appreciated.

Regards,

Sagar

[v-xianjtan-msft]

Hi @sagarsahoo_123 

Please provide sample data that would completely cover your question instead of a screenshot of a visualization chart. If possible, sharing your pbix file is best (Remove sensitive data).

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

[sagarsahoo_123]

Hi @v-xianjtan-msft ,

I would definitely share but office firewall does not allow me to share. Even i will provide you the link to download, you won't be able dowload due to firewall restriction. Is there any other way to share the sample file here!

Regards,

Sagar

[v-xianjtan-msft]

Hi @sagarsahoo_123 

You can upload files to a cloud storage (e.g. OneDrive or Google Drive, etc.) and paste the link to that storage location into the post.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Jarvis Tang

[rohit1991]

Hi @sagarsahoo_123 ,

You need to exclude users with blanks and ensure that only those with worktime < 6 hours for all three months are displayed. Modify your DAX measure as follows:

Step 1: Adjust the Last 3 Months Worktime Calculation

Last_3M_WT = 
VAR max_date = MAX(FY_Calendar[Date])
VAR PreviousDates = DATESINPERIOD(FY_Calendar[Date], max_date, -3, MONTH)

VAR Result = 
    CALCULATE(
        [Avg WT], 
        FILTER(
            VALUES(VW_ARC_SKILL[Personal_ID]), 
            COUNTROWS(
                FILTER(
                    VW_ARC_SKILL, 
                    VW_ARC_SKILL[WorkTime] < 6 &&
                    VW_ARC_SKILL[Date] IN PreviousDates
                )
            ) = 3  -- Ensures all 3 months have values
        )
    )

RETURN Result

Step 2: Ensure Avg WT is Defined Properly

Avg WT = AVERAGE(VW_ARC_SKILL[WorkTime])

• This measure ensures that only users who have values for all 3 months with worktime <6 hours appear in the matrix.
• The COUNTROWS = 3 condition ensures only users with no blanks in the selected period are shown.

"The goal is to turn data into information, and information into insight." – Carly Fiorina

🔗 Need Power BI help? Connect on LinkedIn: Rohit Kumar’s LinkedIn

[sagarsahoo_123]

Hi @rohit1991 ,

Thanks for your support, but i am not getting the desired result. Please find the below screenshot after applying code suggested by you.

But i am expecting to show last 3months along with Worktime value in all month like below 1st row.

Please guide if anytthing else needs to be done!

Regards,

Sagar

[PaulDBrown]

Can you please provide non-sensitive sample data?