Forum Discussion

jsha's avatar
jsha
Frequent Visitor
7 years ago

Date table and then slice

Hello forum. First post.  Experienced Excel user. Relatively new to PBI.   I am having a table with approximately 25 000 documents. The table contains (amongst others) the following columns:   -...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

HI jsha,

 

Did you mean create a report throught two date columns?

 

If this is a case, you can take a look at following link about how create a related expand table with detail date records of each rows from original table.

Spread revenue across period based on start and end date, slice and dase this using different dates

 

If above not help, please share some sample data with expected result to help us test and coding formula.

 

Regards,

Xiaoxin Sheng

jsha's avatar
jsha
Frequent Visitor
7 years ago

Hello again. I have come a bit further since last. I still have one table with planned issue dates and actual issue dates.

I have done the following:

 

 

1. I created a separate additional date table

2. I created measures to get the periodic (per day in date table) values:

IFCACTUAL_ALLSELECTED_m =

CALCULATE(
COUNTA('arpt_DCS_DocumentsPlan'[IFCContractorActual]);
USERELATIONSHIP(DateTableDocsPlanned[DateTab];arpt_DCS_DocumentsPlan[IFCContractorActual]);
ALLSELECTED(arpt_DCS_DocumentsPlan[Discipline]))

3. I created measures to get the running totals of the values in step 2:

IFCACTUAL_ALLSELECTED_m running total in Date =
CALCULATE(
sumx('DateTableDocsPlanned';[IFCACTUAL_ALLSELECTED_m]);
USERELATIONSHIP(DateTableDocsPlanned[DateTab];arpt_DCS_DocumentsPlan[IFCContractorActual]);
FILTER(
ALLSELECTED('DateTableDocsPlanned'[DateTab]);
ISONORAFTER('DateTableDocsPlanned'[DateTab];MAX(DateTableDocsPlanned[DateTab]);DESC)))

 

I am almost there. Now I just have to find a way to end the actual line (green line) at today().

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    HI jsha,

     

    You can add a variable to store current row date, then write a if statement to compare today and current date if current date is less than or equal to today.

    IFCACTUAL_ALLSELECTED_m =
VAR currDate =
    MAX ( arpt_DCS_DocumentsPlan[IFCContractorActual] )
RETURN
    IF (
        currDate <= TODAY ();
        CALCULATE (
            COUNTA ( 'arpt_DCS_DocumentsPlan'[IFCContractorActual] );
            USERELATIONSHIP ( DateTableDocsPlanned[DateTab]; arpt_DCS_DocumentsPlan[IFCContractorActual] );
            ALLSELECTED ( arpt_DCS_DocumentsPlan[Discipline] )
        )
    )


IFCACTUAL_ALLSELECTED_m running total in Date =
VAR currDate =
    MAX ( 'DateTableDocsPlanned'[DateTab] )
RETURN
    IF (
        currDate <= TODAY ();
        CALCULATE (
            SUMX ( 'DateTableDocsPlanned'; [IFCACTUAL_ALLSELECTED_m] );
            USERELATIONSHIP ( DateTableDocsPlanned[DateTab]; arpt_DCS_DocumentsPlan[IFCContractorActual] );
            FILTER (
                ALLSELECTED ( 'DateTableDocsPlanned'[DateTab] );
                ISONORAFTER (
                        'DateTableDocsPlanned'[DateTab]; MAX ( DateTableDocsPlanned[DateTab] ); DESC
                )
            )
        )
    )


    Regards,

    Xiaoxin Sheng