Disconnected Date Slicer

[seryan]

Hello

Looking for some help. I have a table called D_TIME[Date]. This table is imported as a single column Date table and I have set it to be a Date table type in PBI.

I have two other tables, D_SERVICEand F_SERVICE. F_SERVICE has SERVICE_KEY and EFFECTIVE_FROM_DATE as it's primary key columns. D_SERVICEalso has the SERVICE_KEY(only) as it's primary key. They are joined together as a many (Fact) to 1 (Dim) as the Fact table has updates to it's metrics overtime but the DIm is a type 1 table.

So what i am trying to do, is with a date slicer take the SELECTEDVALUE(D_TIME[Date]) and apply it logically to the fact table for use in measures. This principally works and this is the DAX

REVENUE $ =
CALCULATE (
    SUM ( 'F_SERVICE'[TOTAL_CONTRACT_VALUE] )
        + SUM ( 'F_SERVICE'[TOTAL_ONE_OFF_CHARGES] ),
            FILTER (
                'F_SERVICE',
                FORMAT('F_SERVICE'[EFFECTIVE_FROM_DATE],"YYYY-MM-DD") <= FORMAT(SELECTEDVALUE( 'D_TIME'[Date] ),"YYYY-MM-DD")
                    && (
                        FORMAT(MIN( 'D_SERVICE'[CANCELLATION_DATE] ),"YYYY-MM-DD")
                            >= FORMAT(SELECTEDVALUE( 'D_TIME'[Date] ),"YYYY-MM-DD")
                            || ISBLANK ( MIN( 'D_SERVICE'[CANCELLATION_DATE] ))
                    )
            )
)

The problem with this is the calculation is a summarisation on the Fact table. But when I involve non-Fact information in the visualisation, it appears the relationship between D_SERVICE and F_SERVICE is ignored. Just looking for some help on this.

There is no relationship set between D_TIME and any of these other 2 tables because the idea is I want to select a date and do this fancy between logic dynamically. This is a large dataset so this is a directquery to Snowflake re-Dim/Fact Service tables.

[seryan]

The challenge with that approach is I also need to do previous period comparisons which may not have been the active record so filtering the chart directly doesn't really help it has to be basically done on the fly via DAX.

[Anonymous]

Hi @seryan ,

Please have a try.

One possible solution is to use a measure that calculates the active services for each date, and then use that measure as a filter for your visuals. For example, you can create a measure like this:

Active Services =
VAR SelectedDate =
    SELECTEDVALUE ( D_TIME[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( F_SERVICE[SERVICE_KEY] ),
        FILTER (
            F_SERVICE,
            F_SERVICE[EFFECTIVE_FROM_DATE] <= SelectedDate
                && (
                    F_SERVICE[CANCELLATION_DATE] >= SelectedDate
                        || ISBLANK ( F_SERVICE[CANCELLATION_DATE] )
                )
        )
    )

 This measure will return the number of distinct service keys that are active on the selected date, based on the effective from date and cancellation date in the fact table. You can then use this measure as a filter for your visuals, and set the filter condition to be greater than zero. This way, you can ensure that only the active services are shown in your visuals, regardless of the non-fact information.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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