I want to show value from start date till now

[eliasayy]

Hello eevryone i have a company A,B,C,D which invested on 8/1/2022, then Company E invested on 9/1/2022 , and date table which shows all dates

i made a relationship between the tables but when i select september, on the date slicer, only company B shows, and if i choose a day slicer, if i choose august 2, i dont have data, how can i fix? 

 

so i want the date to keep creating with them same value until i create a variable that stops for example, Investor A wants to withdraw on 10/1/2022 so after 9/30/2022, investor A must stop producing data so investor A last date must be 9/30/2022

 

[v-yueyunzh-msft · Accepted solution]

Hi, @eliasayy 

We can see that you created a one-to-many relationship between the original table and the date table.

First of all, one-end table can filter multi-end tables. When you use date as the dimension, if you select August 2, then only the data corresponding to August 2 will be displayed, and the previous data will not be displayed. If there is no data on August 2, it will be displayed as empty.

Secondly, you want to set a value to specify the withdrawal date of a certain company. I am not very sure how you provide the date. Based on your scenario and description, I created a "WithDraw" table to simulate the withdrawal date of the company.

For your question, my understanding is to select a date and display the company's value up to today (unless the company has a withdraw date).

Here are the steps you can follow：

(1)This is my test date : ‘Investors’   ‘WithDraw’     ‘Table’

Table = CALENDAR( FIRSTDATE('Investors'[Date]) , TODAY())

(2) Below is the relationship before the table, we don't need to establish the relationship between 'Investors' and 'Date' table:

(3)We can click “New Measure” to create a measure : ‘isDisplay’

isDisplay =
VAR _select_date =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _invest_date =
    SELECTEDVALUE ( 'Investors'[Date] )
VAR _withdraw_date =
    SELECTCOLUMNS (
        FILTER (
            'WithDraw',
            'WithDraw'[Investment Comapany]
                = SELECTEDVALUE ( 'Investors'[Investment Company] )
        ),
        "withdraw_date", [WithDraw Date]
    )
RETURN
    IF (
        _withdraw_date = BLANK ()
            && _select_date > _invest_date,
        1,
        IF ( _select_date < _withdraw_date && _select_date > _invest_date, 1, -1 )
    )

(4)We can put the measure in the “Filter on this visual” and configure it and then click “Apply filter”:

(5) The result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

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