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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.