Forum Discussion

royalswe's avatar
royalswe
Icon for Helper I rankHelper I
5 years ago
Solved

Manage multiple start and stop date in the same row

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?

 

 

  • 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.

2 Replies