how to set a what if parameter for a date field

[frknklcsln]

Hi,

I want to create a parameter for a date field. The user will choose the date range, and my calculations will change according to it.

The date range will actually be depending on a certain column from my table. How can I achieve this

SUMMARIZE(
    FILTER(
        '10-CSV',
        '10-CSV'[Actual Date for End] <> BLANK() &&
        AND('10-CSV'[Actual Date for End] >= EOMONTH(TODAY(), -2),
        '10-CSV'[Actual Date for End] <= EOMONTH(TODAY(), -1 )),

I will change my dax to below:

SUMMARIZE(
    FILTER(
        '10-CSV',
        '10-CSV'[Actual Date for End] <> BLANK() &&
        [Date Parameter] ),

So, the calculation will be filtered by the user.

When I create the parameters with numeric range. I should convert this 48123 kind of date info to a normal date format like 12/05/2022. 

Any ideas?

[lbendlin]

It's not really worth trying to bend the What-If parameters like this.  Use a disconnected dates table with the appropriate range and feed the date range slicer from that.  Then use SELECTEDVALUE() in your measures.

[frknklcsln]

Hi @lbendlin , thank you for your answer.

I created 2 date table without relation. One for is start date, the other one is end date. Then I created measures as

Start Date = SELECTEDVALUE('Date Table2'[Date])

End Date = SELECTEDVALUE('Date Table3'[Date])

Then I added these measure to the filter in my main DAX like below:

'10-CSV'[Actual Date for End] <> BLANK() &&

        '10-CSV'[Actual Date for End] >= [Start Date] &&

        '10-CSV'[Actual Date for End] <= 'Date Table3'[End Date] &&

But it doesn't work. 

What am I doing wrong?

[lbendlin]

Create a single Dates table. Feed a range slicer from it. Use the MIN() and MAX() values in your measure.

[frknklcsln]

Hi,

I created a data table with single column. Then I wrote measures with MIN and MAX as you said.

But when I added these measure in filters, it doesnt work.

SUMMARIZE(

    FILTER(

        '10-CSV',

        '10-CSV'[Actual Date] <> BLANK() &&

        AND('10-CSV'[Actual Date for End]>= [Start Date],

        '10-CSV'[Actual Date for End]<= [End Date]) &&

        '10-CSV'[Air] =1),

It takes the all none blank values. 

I created a column Column = [Start date] to see which date the measure was taking.

It looks like when i change the range, it doesnt apply. Start date measure will take the MIN value of the whole date table, not the one selected. Any idea?

[lbendlin]

Your approach seems reasonable.  Are you sure you used measures and not calculated columns?

Try 

SUMMARIZE(
    FILTER(
        '10-CSV',
        '10-CSV'[Actual Date] <> BLANK() &&
        '10-CSV'[Actual Date for End]>= min('Date Table2'[Date]) &&
        '10-CSV'[Actual Date for End]<= max('Date Table2'[Date]) &&
        '10-CSV'[Air] =1),

[frknklcsln]

Hi @lbendlin , yes, I am sure I am using measure.

When I did like yours, it doesnt apply too.

My main DAX is virtual table actually. Do you think that may be the reason behind it?

Virtual table =

var _table1=

SUMMARIZE(
FILTER(
'10-CSV',
'10-CSV'[Actual Date for End] <> BLANK() &&
AND('10-CSV'[Actual Date for End]>= MIN('Date Table2'[Date]),
'10-CSV'[Actual Date for End]<= MAX('Date Table2'[Date])) &&
'10-CSV'[Air] =1),
'10-CSV'[ID],
.
.
.
var _ table2= 
.
.
.

[lbendlin]

Virtual tables (table variables) can only live inside a measure, as part of the computation. What you probably did was create a calculated table. These are immutable.