Date table and then slice

[jsha]

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:

- Doc ID

- Planned issue date

- Actual issue date

- Discipline

I am trying to make a Line Chart showing planned curve and actual curve, sliceable on discipline.

There are 19 different disciplines. I hope there is a way of doing this without making 19 different tables.

I suppose step one is to build a separate date table (?)

Any suggestions on strategy?

-jens hauge

[Anonymous]

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]

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]

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

[PattemManohar]

@jsha Please try using the "Discipline" column as slicer (You can select this from the list of available visualizations)

[jsha]

Hello and thank you for your response. So far I have done following:

1. Built separate date table

2. Added two columns with periodic (per day) results in the date table (counta of planned issue date = date in datetable and counta of actual issue date = date in datetable)

3. Added two running totals (to count the results in step 2)

The original table and the datetable are 1:many both way related on the Planned issue date / Date.

This gives a correct total curve. But once I try to slice it on disciplines the results are completely wrong.