How to calculate Total No. of Tasks Active at the end of Selected Period using DAX in Power BI

How to calculate Total No. of Active Tasks during the Selected Period using DAX in Power BI

Scenario:
Let's say we have the Tasks Summary dataset with details like [No. of Tasks] as of [Task Start Date] and their [Task End Date].

The sample data in the Dataset, with No. of Tasks and their Start Dates and End Dates are as follows:

There is a one 1 task with start date as 01-Jan-2021 and end date as 05-Jan-2021.

There are 5 tasks with start date as 03-Jan-2021 and end date as 10-Jan-2021.

There is one 1 task with start date as 03-Jan-2021 and end date as 07-Jan-2021.

There are 4 tasks with start date as 04-Jan-2021 and end date as 07-Jan-2021.

There are 3 tasks with start date as 05-Jan-2021 and end date as 12-Jan-2021.

There are 2 tasks with start date as 06-Jan-2021 and end date as 15-Jan-2021.

There are 4 tasks with start date as 07-Jan-2021 and end date as 18-Jan-2021.

There are 3 tasks with start date as 08-Jan-2021 and end date as 11-Jan-2021.

There are 4 tasks with start date as 08-Jan-2021 and end date as 12-Jan-2021.

There are 4 tasks with start date as 08-Jan-2021 and end date as 20-Jan-2021.

There is one 1 task with start date as 12-Jan-2021 and end date as 23-Jan-2021.
There is one 1 task with start date as 12-Jan-2021 and end date as 26-Jan-2021.
There is one 1 task with start date as 12-Jan-2021 and end date as 19-Jan-2021.

From this Sample, we understand that some tasks are staring on a Start Date and some tasks are close on the same Start Date.

Examples:
On Task_Start_Date: 05-Jan-2021, 3 new tasks are Starting and 1 previous task is Closing as its Task_End_Date is 05-Jan-2021.

Similarly, on Task_Start_Date: 07-Jan-2021, 4 new tasks are Starting and 5 previous tasks are Closing as their Task_End_Date is 07-Jan-2021.

Similarly, on Task_Start_Date: 12-Jan-2021, 3 new tasks are Starting and 7 previous tasks are Closing as their Task_End_Date is 12-Jan-2021.

With this understanding, now we will calculate the Total No. of Tasks that are active during the Selected Period, and the actual No. of Tasks active at the end of the Selected Period using the below DAX Logics based on the following Data Model:

[No. of Tasks on StartDate] = SUM(Tasks_Summary[No_of_Tasks])

The following Measure returns the Cumulative Total No. of Tasks by dim_Date[Date]:

Cumulative No. of Tasks on StartDate =

VAR _MaxSelectDate = MAX(dim_Date[Date])

VAR _Cumulative_Tasks =

CALCULATE( [No. of Tasks on StartDate],

    dim_Date[Date] <= _MaxSelectDate,

    REMOVEFILTERS(Tasks_Summary)    

    )

RETURN

IF(NOT ISBLANK([No. of Tasks on StartDate]),_Cumulative_Tasks)

The following measure returns "Y", if any Tasks are Closing on that selected Task_Start_Date:

Is any Task Closing on Selected StartDate =

VAR _Max_Date = MAX(Tasks_Summary[Task_StartDate])

VAR _Min_Task_Date =

CALCULATE(

        MIN(Tasks_Summary[Task_EndDate]),

        FILTER(

            ALL(Tasks_Summary),

            Tasks_Summary[Task_EndDate] = _Max_Date

        )

    )

VAR _Result = IF ( NOT ISBLANK(_Min_Task_Date),"Y","N")

RETURN

IF( NOT ISBLANK(_Max_Date), _Result)

The following measure returns No. of Tasks that are Closing on that selected Task_Start_Date:

No. of Tasks Closing on Selected StartDate =

VAR _Max_Date = MAX(Tasks_Summary[Task_StartDate])

VAR _Result =

    CALCULATE(

        [No. of Tasks on StartDate],

        FILTER(

            ALL(Tasks_Summary),

            Tasks_Summary[Task_EndDate] =_Max_Date

        )

    )

RETURN

IF( NOT ISBLANK(_Max_Date), _Result)

The below measure returns the total No. of  Active Tasks including Closed tasks during the selected Date Period.

No. of Tasks Active during the Selected Period =

VAR _MinSelectDate = MIN(dim_Date[Date])

VAR _MaxSelectDate = MAX(dim_Date[Date])

VAR _MaxTaskDate = MAX(Tasks_Summary[Task_StartDate])

VAR _Result =

    CALCULATE(

        [No. of Tasks on StartDate],

        REMOVEFILTERS(dim_Date[Date]),

        Tasks_Summary[Task_StartDate] <= _MaxSelectDate,

        (

        Tasks_Summary[Task_EndDate] > _MinSelectDate

        ||

        ISBLANK(Tasks_Summary[Task_EndDate])

        )

    )

RETURN

IF(NOT ISBLANK(_MaxTaskDate),_Result)

The below measure returns the total No. of Active Tasks excluding Closed tasks at the end of the selected Date Period.

No. of Tasks Active at the end of Selected Period =

VAR _MaxSelectDate = MAX(dim_Date[Date])

VAR _MaxTaskDate = MAX(Tasks_Summary[Task_EndDate])

VAR _Result =

    CALCULATE(

        [No. of Tasks on StartDate],

        REMOVEFILTERS(dim_Date[Date]),

        Tasks_Summary[Task_StartDate] <= _MaxSelectDate,

        (

        Tasks_Summary[Task_EndDate] > _MaxSelectDate

        ||

        ISBLANK(Tasks_Summary[Task_EndDate])

        )

    )

RETURN

IF( NOT ISBLANK(_MaxTaskDate), _Result )

Result:

The result of the above Measures are as per below:

The fields [Date], [Month_Year] are considered from Dim_Date table.

The fields [Task_StartDate] and [Task_EndDate] are considered from Task_Summary table.

The results of the following measures are seems to be similar in the above Table visual. However, when we look at the Totals, they returns different results.

[No. of Tasks Active during the Selected Period] : It Sum up all the Active Tasks, including all the Closed tasks and returns the total no. of tasks during the selected date period.
[No. of Tasks Active at the end of Selected Period] : It Sum up the Active Tasks, excluding all the Closed Tasks and returns the final total no. of tasks at the end of the selected date period.

The result of Total No. of Tasks from the above measure can be viewed using Column Charts as shown below:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------

How to generate list of Dates available between Start Date and End Date using Power Query

How to create list of Dates falls between Start Date and End Date using Power Query
Scenario:
Let's say we have two date columns in a Dataset as Task_Start_Date and Task_End_Date as shown below.

Now we need to generate a new Date column as "Task_Active_Dates" based on the no. of Dates falls between Task_Start_Date and Task_End_Date.

For example, Task_Start_Date = 01-Jan-2021 and Task_End_Date= 05-Jan-2021, then there are 5 no. of dates falls between those dates (inclusive of Start and End Dates).

We can achieve this Scenario, using the below Power Query logic:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddDLCQAhDATQXnJ2IYn/WsT+23DZuYwLAS/jk4m6lqg973J1kyRaKZjs9PdGweHOPilkeI76C7xEXuE16m9wvpIphQ7v7PyYAR9R/4Tzll3/o9+Ba6aVa5rsfQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Task_Start_Date = _t, Task_End_Date = _t, Task_Owner_ID = _t]),

    

ChangeType = Table.TransformColumnTypes(Source,{{"Task_Start_Date", type date}, {"Task_End_Date", type date}, {"Task_Owner_ID", Int64.Type}}),

TaskActiveDates =
Table.AddColumn(ChangeType, "Task_Active_Dates",
    each List.Dates(

    [Task_Start_Date], 

    Duration.Days([Task_End_Date]-[Task_Start_Date])+1,

    #duration(1,0,0,0)

)),

    ExpandDates = Table.ExpandListColumn(TaskActiveDates, "Task_Active_Dates")

in

    ExpandDates

Result:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------

How to Hide or Mask the Measure Values with Object Level Security in Power BI

How to implement Object Level Security to Hide or Mask the Measure Values in Power BI
To implement Object-Level Security (OLS) in Power BI, define security roles directly in Power BI, and then use Tabular Editor to manage access to the tables and columns by applying the appropriate filters and permissions.
Example (Scenario):
Let's assume the Power BI data model is structured as shown below.

The relationships are as per below:

The Measure are as per below:

Net Revenue = SUM(fact_Sales_Orders[Revenue])

Quantity Sold = SUM(fact_Sales_Orders[Quantity_Sold])

Stock Value = SUM(fact_Inventory[Stock_Valuation])

Now we need to implement the following to the measures by using the Object Level Security.
1. Hide or restrict the [Stock Value] measure to use by users in the Report.

2. Mask the [Net Revenue] measure value as "OLS_Restricted"

We can achieve this scenario by implementing the Object Leve Security (OLS) to measures as explained in below process.

Implementing OLS on a Measure Object Level:

We cannot directly implement the OLS on the Measures. However, we can achieve this using a dummy table object and some flags as explained below.

Step1: Create a Temporary OLS Table:
We will create a temporary table to implement OLS for measures. This table can be hidden.

OLS_Security_Enabler = FILTER({0,1}, TRUE())

Step2: Define a Security Role on table OLS_Security_Enabler:
We will define a Security Role as "OLS_Model_Objects" on the OLS_Security_Enabler table as shown below:

OLS_Security_Enabler[Value] == 1

Step3: Define the required Flag Measures:

We need to create a flag measure as OLS_Flag with below logic will always return value as 1.
This flag will be used in the measures for which we want to apply OLS to Hide or restrict them for users in the Report:

OLS_Flag = (COUNTROWS(VALUES(OLS_Security_Enabler[Value]))<=2)*1

We need to create a flag measure as Is_OLS_Applied with below logic will return value as 1, when the OLS Security is applied in the Report. Otherwise, it returns values as 0.

This flag will be used in the measures for which we want to apply OLS to mask (not hide) their values with custom value as "OLS_Restricted"

Is_OLS_Applied = (COUNTROWS(OLS_Security_Enabler) = 1)*1

Step4: Apply the OLS Flag Logics to Measures:
As per the requirement, to restrict or hide [Stock Value] measure, we use the OLS_Flag in the measure definition as per below.

Stock Value =

VAR _Stock_Val = SUM(fact_Inventory[Stock_Valuation])

VAR _OLS_Enabled = [OLS_Flag]

VAR _No_Blank = (NOT ISBLANK(_Stock_Val))*1

RETURN

IF( _OLS_Enabled=1, _Stock_Val)

To Mask (not hide) the [Net Revenue] measure value, we use the Is_OLS_Applied flag in the measure definition as per below.

Net Revenue =

VAR _Revenue = SUM(fact_Sales_Orders[Revenue])

VAR _OLS_Enabled = [Is_OLS_Applied]

VAR _No_Blank = (NOT ISBLANK(_Revenue))*1

RETURN

IF( AND(_OLS_Enabled=1,_No_Blank=1), "OLS_Restricted", _Revenue)

Step5: Implement OLS for the table Column: OLS_Security_Enabler[Value]:

First Open the Power BI Data Model with Tabular Editor tool to manage the OLS permissions.

Next select the column [Value] from table OLS_Security_Enabler then go to Object Level Security property under Section of [Translations, Perspectives, Security].
Next set the OLS_Model_Objects security value as "None".

Note:
In this case, we set the Object level security, either to the table OLS_Security_Enabler or to the column [Value].
Since this table is having only 1 Column, entire table will be hidden when column is hidden.

Final Step: Test the Role:

Now we can test the role and see the results before and after OLS as per below.

Result: Before OLS

Result: After OLS

Notes: 

The [Stock Value] measure is restricted and hidden, hence the dependent visuals are broken.
The [Net Revenue] measure value is only masked with the value as "OLS_Restricted", hence the dependent visuals are not affected.

For a detailed understanding on how Object Level Security works for Tables and Columns, please refer the below article from this Blog:

https://excelkingdom.blogspot.com/2025/03/how-to-implement-object-level-security.html

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------