DATEDIFF with timestamps and filters

[Anonymous]

Each QTE # will go through various States. Order of States goes as is -- Open->With Supplier->Buyer Review->Complete/Closed Complete. The Start of State will equal the End of the State it was previously in.

WHAT I AM TRYING TO DO:

-I want to get the Datediff of the Start when a Quote is in Open (When the quote starts) to the Start of Complete (when it is done) -- IF THERE IS NO Complete State then used Closed Complete Start date.

-I have 3 quotes in the image below to show--numbers i want:

(QTE0005211 = 5 DAYS; QTE0005227 = 15 DAYS)

-ignore QTE0005212 --it has 2 complete  states (very uncommon)

-I know the code entered is doing what its supposed to - but i know i need more to get the results explained above

[sturlaws · Accepted solution]

Hi @Anonymous,

different ways of solvings this, you don't provide to much details about your requirements.

If you want to solve with a measure, you could try something like this:

number of days =
VAR startdate =
    CALCULATE (
        MIN ( Quotes[start] );
        FILTER (
            ALL ( Quotes );
            Quotes[state] = "open"
                && Quotes[qte] = MAX ( Quotes[qte] )
        )
    )
VAR enddate_complete =
    CALCULATE (
        MIN ( Quotes[start] );
        FILTER (
            ALL ( Quotes );
            Quotes[state] = "complete"
                && Quotes[qte] = MAX ( Quotes[qte] )
        )
    )
VAR enddate_closedcomplete =
    CALCULATE (
        MIN ( Quotes[start] );
        FILTER (
            ALL ( Quotes );
            Quotes[state] = "closed complete"
                && Quotes[qte] = MAX ( Quotes[qte] )
        )
    )
RETURN
    IF (
        ISBLANK ( enddate_complete );
        DATEDIFF ( startdate; enddate_closedcomplete; DAY );
        DATEDIFF ( startdate; enddate_complete; DAY )
    )

[Anonymous]

@sturlaws 

Thank you! I thought i would be been able to go on from here but I adjusted my code to have a different start date which shouldnt have changed anything. I am trying to visualize these in a bar chart on average days by month

GOAL: (On average how long does an RPA take from start (Start PRT) to finish (enddate_complete or enddate_closedcomplete) BY month.)

I split the variables from NUMBER OF DAYS into columns to see if that could help (no data comes up unless i take out the MAX ( 'QTE State'[RPA #] ) part of the filter then it just returns the same date for every row (shown in 5th column.)

The enddate_complete and enddate_closedcomplete are MEASURES (the variables) the enddate_complete1 is a COLUMN but isnt working how i wish it would like the measure. I am using the months based on the Start PRT column.

I only need one bar. i am just showing 2 to show you how its represented w the code.

AVG of NoD = AVERAGEX ('QTE State', 'QTE State'[NUMBER OF DAYS])

I am using the months based on the Start PRT column. The enddate_complete and enddate_closedcomplete are MEASURES (not columns) the enddate_complete1 is a COLUMN but isnt working how i want.

[sturlaws]

Hi, I am a bit confused about your post. You want to find the average number of days, and the rest of your writing is things you have tried which did not work out?

Could you try something like this:

a_nod = AVERAGEX(ADDCOLUMNS(VALUES('Table'[RPA]);"nod";[number of days]);[nod])

where [number of days] is a measure?

[Anonymous]

@sturlaws Let me start over. 

In the image i have above, June is the month that is currently selected -- so all the Start PRT dates of that selection should be in June. I just want one bar that shows the average of the days selected--here it should be 23 days (230/10).

The code last sent did not make sense to me.

[sturlaws]

sorry, still at loss at what you are trying to achieve.

Could you create and upload a sample pbix-file?

[Anonymous]

In the column NUMBER OF DAYS. All i want is the average of those days by month. In the image i have June selected so i want the average of that (38+28+33+5+28+28+24+25+10+11)/10 = 23 represented that in a bar chart by month, where for June it would be 23 days. The numbers in each bar (only want 1) 38 & 24 seem to just be a selection from the data.

[sturlaws]

average_number_of_days =
AVERAGEX (
   ADDCOLUMNS ( VALUES ( 'QTE State'[RPA #] ); "number of days pr RPA #"; [Number of days] );
   [number of days pr RPA #]
) 

where [Number of days] is the measure you have already created

[Anonymous]

@sturlaws 

i am confused by the bolded/underlined -- where are you getting this from?

average_number_of_days =
AVERAGEX (
   ADDCOLUMNS ( VALUES ( 'QTE State'[RPA #] ); "number of days pr RPA #"; [Number of days] );
   [number of days pr RPA #]
) 

[sturlaws]

The ADDCOLUMNS-function can add a column to a table. In this case I create a table with one column with the VALUES-function. And then I add a column. This added column needs to be given a name, it can be named anything you want. I then add the measure [number of days]. The output of the ADDCOLUMNS-function will then be a table with the unique values of 'QTE State'[RPA #] and the [number of days] for each [RPA #]. This you can verify by taking the ADDCOLUMNS-part of the dax expression and hit the 'New Table' in the modelling pane in Power BI Desktop.

The AVERAGEX-function needs a table expression as first argument, which in this case is the resulting table from ADDCOLUMNS. The added column, [number of days pr RPA#], is available for AVERAGEX, so the average is made over this column.

In your example I see that you have some RPA# which have multiple occurences, I am not sure how this will behave in this case.  You might have to change VALUES to SUMMARIZE in order to get multiple lines for such a RPA#. But then you would also have to include  something like start date.