Create a date measure as a filter on power BI

[Anonymous]

I need to set a date measure as a filter in filter pane.My measure is as below,

max&mindate =
VAR minDate =
    CALCULATE ( MIN ( DateTable[Date] ) )
VAR maxDate =
    CALCULATE ( MAX ( DateTable[Date] ) )
RETURN
    IF (
        MAX ( DateTable[Date] ) >= minDate
            && MAX ( DateTable[Date] ) <= maxDate,
        minDate & " " & maxDate
    )

The out put is minimum date and maximum date in date table.Is there a way to do this? Simply i need to set this measure as a filter So the user can tick this if he wants to see the data under this dates.

Thanks

[Anonymous]

Hi @Anonymous,

Can you please provide some dummy data with the expected result to help clarify your data structure and test coding formula on it? I think it will help us to solve your question quickly.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @Anonymous 

Im having a visual like this,

It uses Dates in date table in x axis.I need to show this visual if user needs like this,

So my main concern is to find a easy way to let user  choose this option whether to display by months or only january first and July 31(todate). I created a measure like this to get these dates,

Measure 3 =

VAR minDate =

CALCULATE ( MIN ( DateTable[Date] ) )

VAR maxDate =

CALCULATE ( MAX ( DateTable[Date] ),ALLSELECTED(DateTable ))

RETURN

IF (

MAX ( DateTable[Date] ) >= minDate

&& MAX ( DateTable[Date] ) <= maxDate,

minDate & " " & maxDate

)

I think the best way is to create a calculated column and let user to decide the dates.But im finding a best solution for this.

Thanks,

Mish

[amitchandak]

@Anonymous , something like like

max&mindate =
VAR minDate =
MINX (allselected(DateTable), DateTable[Date] )
VAR maxDate =
MAXX(allselected(DateTable), DateTable[Date] )
RETURN
minDate & " " & maxDate

[Anonymous]

@amitchandak ,

Thanks for the measure.Its giving the same results as my measure.I cannot put this measure as a filter.Its only giving the results on a card visual.I want to put this as a filter to tick or untick.I dont know if its possible 🙂

Thanks

[Fowmy]

@Anonymous 

It seems you want the user to select all the dates in the calendar, why don't you use the date slicer itself. If you click on the clear button or click on SELECT ALL, it does the same thing you need.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Anonymous]

Hi@Fowmy ,

Im already having a date slicer.I need to add a measure which should react to the date slicer as well seperately in the filter pane.It should be a choice of user.

Thanks

[Fowmy]

@Anonymous 

I am not clear about what you are trying to achieve. 

Use the below measure and put in the slicer filter pane and set to 1 as per your need and check if you get to where you want.

max&mindate = 
VAR minDate =
    CALCULATE ( MIN ( 'CAlendar'[DATE] ) )
VAR maxDate =
    CALCULATE ( MAX ('CAlendar'[DATE] ) )
RETURN
    IF (
        MAX ('CAlendar'[DATE] ) >= minDate
            && MAX ('CAlendar'[DATE] ) <= maxDate,
        //minDate & " " & maxDate
    1,0
    )

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Anonymous]

Hi @Fowmy ,Thanks for your measure.

By default my visual look like this,

and i need to show by choice as this,

That is what i want to do in the measure.So the measure should be as filter to tick or untick.

Thanks

[Fowmy]

@Anonymous 

Ticket and Untick are options in the slicer, slicers accept Field values, it should be from a table.

Check the interaction setting under formatting tab to ensure your date slicer is filtering the Visual like the example below

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn