cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SaurabhGupta Frequent Visitor
Frequent Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
v-jiascu-msft Super Contributor
Super Contributor

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Highlighted
rajendran Super Contributor
Super Contributor

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

SaurabhGupta Frequent Visitor
Frequent Visitor

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?

v-jiascu-msft Super Contributor
Super Contributor

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 141 members 1,535 guests
Please welcome our newest community members: