Formula help

[mazwro]

I am attempting to recreate a formula and I am not sure what I am doing wrong.

I am trying to calculate a number of incidents which have been open prior to the selected date range, and closed after that time, or not closed at all.

So that when I select Oct2024, I see incidents which were open at the beginning of MArch and not closed in MArch, or at all, same for any other month

I have a dim_dat slicer with months selected : e.g. October 2024, this dim table links to my vw_PM_Dashboard on dim date = date_problem_created, relationship active, both dates are in format DATE

In SQL I am doing a check as follows:

Select distinct IDINC
from [dbo].[vw_PM_Dashboard] 
where [Date_Problem_Created] < '2023-10-01 00:00:00'
and ([Date_Problem_Closed] > '2023-11-01 00:00:00' or [Date_Problem_Closed]  is null )

And i have a measure in PBI:

Opened Before Selected Period and Unsolved in Period= 
VAR SelectedStartDate = MIN(dim_date[Date]) -- Get the start date from the slicer
VAR SelectedEndDate = MAX(dim_date[Date]) -- Get the end date from the slicer
return
CALCULATE(DISTINCTCOUNT(vw_PM_Dashboard[IDINC]),
'vw_PM_Dashboard'[Date_Problem_Created] < SelectedStartDate,
'vw_PM_Dashboard'[Date_Problem_Closed] > SelectedEndDate
)

 
I have no clue why SQL shows me the right amount - approx 1k IDs, and measure in power BI just shows Blank.

Could anyone please shed some light? How can I solve it? Thanks!

** I noticed that the problem is that this formula filters incidents to thos opened in my date period so I adjusted my formula: but this just gives me blanks.

Count_Open_Problems =

VAR SelectedStartDate = MIN(dim_date[Date]) -- Get the selected start date from the slicer

VAR SelectedEndDate = MAX(dim_date[Date]) -- Get the selected end date from the slicer

RETURN

CALCULATE(

    DISTINCTCOUNT(vw_PM_Dashboard[IDINC]),

    'vw_PM_Dashboard'[Date_Created] < SelectedStartDate, -- Include all problems created before or on the end date

    OR(

        'vw_PM_Dashboard'[Date_Closed] > SelectedEndDate, -- Closed on or after the start date

        ISBLANK('vw_PM_Dashboard'[Date_Closed]) -- Still open (no close date)

    ),

    REMOVEFILTERS(dim_date[Date]) -- Ensure the slicer does not filter open dates

)

[v-yaningy-msft]

Hi, @mazwro 

Thanks for the reply form @rajendraongole1. You can provide some pbix files or tables without sensitive data, the expected output based on that data and the logic to achieve that effect, which will help you solve the problem faster. Thanks for your understanding.

Here are some notes:
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Best Regards,
Yang
Community Support Team

[rajendraongole1]

Hi @mazwro  - CALCULATE does not directly process OR logic as expected when used inside its filter arguments. Instead, it evaluates each condition independently, which can lead to unexpected results like blank values.

Count_Open_Problems =
VAR SelectedStartDate = MIN(dim_date[Date]) -- Start date from slicer
VAR SelectedEndDate = MAX(dim_date[Date]) -- End date from slicer
RETURN
CALCULATE(
DISTINCTCOUNT(vw_PM_Dashboard[IDINC]),
FILTER(
vw_PM_Dashboard,
vw_PM_Dashboard[Date_Created] < SelectedStartDate && -- Created before the selected period
(
vw_PM_Dashboard[Date_Closed] > SelectedEndDate || -- Closed after the selected period
ISBLANK(vw_PM_Dashboard[Date_Closed]) -- Or still open
)
),
REMOVEFILTERS(dim_date[Date]) -- Ignore slicer filters on the date dimension
)

Cross-check the results in SQL and Power BI to ensure the data matches as expected.

Hope this helps.

[mazwro]

hello @rajendraongole1 thank you for the help. I am not sure if this is an error though.

For throubleshoot, I tried with a simpler query, where for slicer Month I get counts of incidents opened before the start date and closed after the end date and this also gives me blanks and I do not understand why. Can you help?

VAR SelectedStartDate = MIN(dim_date[Date]) -- Start date from slicer
VAR SelectedEndDate = MAX(dim_date[Date]) -- End date from slicer
RETURN
CALCULATE(
DISTINCTCOUNT(vw_PM_Dashboard[IDINC]),

vw_PM_Dashboard,
vw_PM_Dashboard[Date Created] < SelectedStartDate && -- Created before the selected period
vw_PM_Dashboard[Date Closed] > SelectedEndDate-- Closed after the selected period
,
REMOVEFILTERS(dim_date[Date]) -- Ignore slicer filters on the date dimension
)