Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
SaurabhGupta
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

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.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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

 

Thanks

Raj

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?

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.