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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.