DAX Measure Calculation – Returning Empty Values

[toum]

I'm trying to create a DAX measure that applies a set of conditions based on selected dates, but the result always returns an empty value, even when there should be data based on the applied filters. Here's what I'm trying to achieve:

START DATE <= MinSelectedDate
AND END DATE >= MaxSelectedDate
AND (
    (Start pd >= START DATE AND END pd < MinSelectedDate) OR  
    (Start pd >= MinSelectedDate AND END pd <= MaxSelectedDate AND TYPE = 0)
)
AND date >= Start pd
AND date <= End pd

I've created a measure with these conditions, but I'm not seeing the expected results. It keeps returning blank value

CALCULATE(
    SUM(tab[sales]),
    tab[START DATE] <= [minselecteddate],
    tab[END DATE] >= [maxselecteddate],
    (
        (tab[Start pd] >= tab[START DATE] && tab[END pd] < [minselecteddate] ||
        (tab[Start pd] >= [minselecteddate] && tab[END pd] <= [maxselecteddate] && tab[TYPE] = 0)
    ),
    tab[date] >= tab[Start pd],
    tab[date] <= tab[End pd]
)

Some sample data

The minselecteddate and maxselecteddate are measures created that, in my case, represent the minimum date selected in the slicer used in the report.

minselecetddate=CALCULATE(min('Date dimension'[Date]), ALLSELECTED('Date dimension'[Date]))
maxselecteddate=CALCULATE(max('Date dimension'[Date]), ALLSELECTED('Date dimension'[Date]))

Can anyone help identify why this might be happening and how I can solve this?

Thank you.

[nandic]

@toum
i tried to replicate demo on my side (next time just add data in csv format instead of image, it will be much easier).

I have a few questions and checks
1) calculations for min and max selected date are correct
2) format of your measure looks like it is actually calculated column, not measure?

Example: CALCULATE( SUM(tab[sales]), tab[START DATE] <= [minselecteddate],

In measure this returns error as tab[Start date] needs to have some kind of aggregation.
If you created it as a measure, please send full Dax for the measure.

3) if you created it as calculated column, then changing slicer (date) will have no impact on this calculated column because calculated column is "older" than slicer. It is created/calculated before slicer. Slicer has impact on measure, but not on calculated column.

Attached results of each condition (pbix and screenshot). I couldn't find any date period where all conditions are met.

Cheers,
Nemanja

[toum]

Thank you a lot for your help

1. yes the minselecteddate and maxselecteddate measures works fine, I get the correct dates
2. Yes it's a measure not a calculated column

[PhilipTreacy]

@toum 

Can you supply some sample data ?

Phil

[toum]

Thank you for your answer, here is an example of the data :

[nandic]

Hi @toum ,
I suggest testing it step by step, variable by variable to show which step actually filters data out.

What is your calculation for minselecteddate and maxselecteddate? Does it return static or dynamic values when you show it in table?
Try testing each of these variables on table with actual data.
Why? Because mostly variable returns correct value when checking using card visual. However, when we put it in table with different dimensions, it actually shows dynamic (different) value per each row, comparing to card visual which shows always static value.

In addition, maybe you should be using ALL statement somewhere. There is possibility that one of parameter conditions is filtering out data.

Cheers,
Nemanja

[toum]

Thank you for your answer. Yes, I tried to create a new column for each condition to check which rows should be counted. I should see some amounts based on the filters I chose, but I always get an empty value. The MinSelectedDate and MaxSelectedDate are measures that, in my case, represent the minimum/max date selected in the slicer used in the report. the date dimension is not linked to the fact table.