Manage multiple start and stop date in the same row

[royalswe]

I have two stop and start dates in the same row and it will probably work to have it that way but I wonder if it is possible to do it in a better way.

The pic bellow are from the raw data with some of the dates. In most cases they have the same dates as in the pic but not in all rows. (1900-01-01 is a default value if anyone is wondering)

It is around 10 fields and around 200,000 rows for the moment, in this table with To and From dates.

 

This can complicate certain situations, for example if I want to slize/filter for only the TP dates or vice versa.

I could hardcode a table with TP and FC values and then create a slizer with theese values.

Then I could use selectedvalue() in my measures to point out which dates I will refer to.

The thing is that it will be longer measures with alot of switch or if cases depending if it is TP, FC or both date types.

Here is a example measure I made that will work but may be unnecessarily complicated

count num of rows for TP or FC or Both = 
VAR __sel = SELECTEDVALUE(TypeTable[Type]) // TP or FC selected from slizer
VAR __currentMonth = FORMAT(TODAY(), "yyyymm")
VAR __NumOfCerts = CALCULATE(
    COUNT(Sales[sales_id]),
    FILTER(
        ALL(SalesTransfer),
        SWITCH(
            __sel,
            "TP", FORMAT(SalesTransfer[TP_From], "yyyymm") = __currentMonth, // if TP is selected
            "FC", FORMAT(SalesTransfer[FC_From], "yyyymm") = __currentMonth,  // if TP is selected
            FORMAT(SalesTransfer[TP_From], "yyyymm") = __currentMonth ||  FORMAT(SalesTransfer[FC_From], "yyyymm") = __currentMonth  // if no selection
        )
    )
)
RETURN COALESCE(__NumOfCerts, 0) // show 0 instead of blank if no rows

The question is, is this the way to go or should I modify the data model in some way?

[royalswe · Accepted solution]

Thanks for your answear @amitchandak 

I ended up fetching each start and stop date in seperate tables, then created a custom column for each attribute name. Finaly appended the tables togheter

Result is a new start & stop table:

This will probably easier to develop a report with.

[amitchandak]

@royalswe , Not sure I got it completely. One way is have one or more independent tables and use then with different dates

measure =
var _max = maxx(allselected('Date'), 'Date'[date])
var _min = Minx(allselected('Date'), 'Date'[date])

return
calculate(sum(Table[Value]), filter(('Table'), 'Date'[Table] =_max )) // add other filter

The second is to join all dates with a date table and use userelationship for inactive joins

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...