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