Reply
Frequent Visitor
Posts: 6
Registered: ‎07-11-2018
Accepted Solution

Change in column's data on the basis of slicer

Hi,

 

Is it possible to create a column which has dynamic data based upon the slicer? For example,

A project has a start date and an end date. I will have a slicer in whcih I will specify a range of dates. Then, my column will give me the number of days that project has in the selected range of dates.


Accepted Solutions
Community Support Team
Posts: 6,627
Registered: ‎05-02-2017

Re: Change in column's data on the basis of slicer

Hi @SaurabhGupta,

 

Try this formula please.

Measure =
VAR minDate =
    MIN ( 'calendar'[date] )
VAR maxDate =
    MAX ( 'calendar'[date] )
VAR startDate =
    MIN ( project[start date] )
VAR endDate =
    MIN ( project[end date] )
RETURN
    SWITCH (
        TRUE (),
        startDate < minDate
            && endDate < minDate, 0,
        startDate > maxDate
            && endDate > maxDate, 0,
        startDate < minDate
            && endDate > maxDate, DATEDIFF ( minDate, maxDate, DAY ),
        startDate < minDate
            && endDate > minDate
            && endDate < maxDate, DATEDIFF ( minDate, endDate, DAY ),
        endDate > minDate
            && endDate < maxDate
            && startDate > minDate
            && startDate < maxDate, DATEDIFF ( startDate, endDate, DAY ),
        endDate > maxDate
            && startDate > minDate
            && startDate < maxDate, DATEDIFF ( startDate, maxDate, DAY )
    )

Best Regards,

Dale

View solution in original post


All Replies
Highlighted
New Contributor
Posts: 626
Registered: ‎11-01-2017

Re: Change in column's data on the basis of slicer

Yes,if your data model has the supporting data, this is possble.

 

Thanks

Raj

Frequent Visitor
Posts: 6
Registered: ‎07-11-2018

Re: Change in column's data on the basis of slicer

Thanks Raj,

Could you please tell me how to do that?

I have Project start date, project end date, and a calendar table (that will be used in a slicer for selecting the range of dates).

How can I create a new column in my project table which will show the number of days that a project has in the selected range of dates?

Community Support Team
Posts: 6,627
Registered: ‎05-02-2017

Re: Change in column's data on the basis of slicer

Hi @SaurabhGupta,

 

Try this formula please.

Measure =
VAR minDate =
    MIN ( 'calendar'[date] )
VAR maxDate =
    MAX ( 'calendar'[date] )
VAR startDate =
    MIN ( project[start date] )
VAR endDate =
    MIN ( project[end date] )
RETURN
    SWITCH (
        TRUE (),
        startDate < minDate
            && endDate < minDate, 0,
        startDate > maxDate
            && endDate > maxDate, 0,
        startDate < minDate
            && endDate > maxDate, DATEDIFF ( minDate, maxDate, DAY ),
        startDate < minDate
            && endDate > minDate
            && endDate < maxDate, DATEDIFF ( minDate, endDate, DAY ),
        endDate > minDate
            && endDate < maxDate
            && startDate > minDate
            && startDate < maxDate, DATEDIFF ( startDate, endDate, DAY ),
        endDate > maxDate
            && startDate > minDate
            && startDate < maxDate, DATEDIFF ( startDate, maxDate, DAY )
    )

Best Regards,

Dale