remove or don't show data after a certain date is reached

[noah44]

Hi,

I am trying to create a dashboard that contains data for each day in 15 minute steps.

I have a table with these 15 minute steps and another table with an end-date. The tables are related by the user ID

The problem is that these 15 minute steps exist for every day but I want to remove/don't show the data after the end-date.

(not every user has an end-date) 

I hope this is understandable

Regards

Noah

[v-binbinyu-msft]

Hi @noah44 ,

Please try below steps:

1. I have create two table for test

Table1:

Table2:

The model:

2. create a measure and add a table visual

Measure =
VAR cur_id =
    SELECTEDVALUE ( Table1[ID] )
VAR cur_end_date =
    SELECTEDVALUE ( Table1[End-Date] )
RETURN
    CALCULATE (
        SUM ( Table2[15 minute steps] ),
        FILTER ( ALL ( Table2 ), Table2[ID] = cur_id && Table2[Date] <= cur_end_date )
    )

If i misunderstand your demands, please feel free to let me know.

I have attached my .pbix file, please refer it.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[noah44]

Hi @v-binbinyu-msft,

thanks for the response.

I am having a problem with all the IDs that have no end-date. There is no data shown for them.

[v-binbinyu-msft · Accepted solution]

Hi @noah44 ,

Only need adjust the measure can achive it.

1. my table:

Table 1:

Table 2:

2.the model is same as above

3.create a measure and add a table visual

Measure =
VAR cur_id =
    SELECTEDVALUE ( Table1[ID] )
VAR cur_end_date =
    SELECTEDVALUE ( Table1[End-Date] )
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( cur_end_date ), SUM ( Table2[15 minute steps] ),
        CALCULATE (
            SUM ( Table2[15 minute steps] ),
            FILTER ( ALL ( Table2 ), Table2[ID] = cur_id && Table2[Date] <= cur_end_date )
        )
    )

Please refer my attached .pbix file

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[noah44]

Hi @v-binbinyu-msft 

thank you so much for your help.

I changed the measure and now it shows all the data as it should

But it doesn't cut away the values that came after the end-date. I tried changing thetype from datetime to date, but the 15 minute steps always need to have datetime.

Regards 

Noah

[v-binbinyu-msft]

Hi @noah44 ,

In my example, I was able to get the correct result for my measure, because you didn't provide detailed data, so I can't tell what your problem is, please adjust it to your actual data according to the example I gave.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.