Home Forums Power Pivot Two date tables for one data table

Tagged: 

This topic contains 10 replies, has 4 voices, and was last updated by  tomallan 2 years, 8 months ago.

Viewing 11 posts - 1 through 11 (of 11 total)
  • Author
    Posts
  • #4455

    IanR
    Participant
    • Started: 8
    • Replies: 7
    • Total: 15

    <span style=”color: #000000; font-family: Calibri;”>Hi,</span>

    <span style=”color: #000000; font-family: Calibri;”>I have an extract from an opportunities tables from CRM. There are several date fields including, for example, the date the opportunity was created and the date that the opportunity was closed. I would like to report, by period, on how many opportunities were opened in a given period and how many opportunities were closed. My columns would be:</span>

    <span style=”color: #000000; font-family: Calibri;”>Month & Year (Rows)</span>

    <span style=”color: #000000; font-family: Calibri;”>Count of opportunities opened (Values)</span>

    <span style=”color: #000000; font-family: Calibri;”>Count of opportunities closed (Values)</span>

    <span style=”color: #000000; font-family: Calibri;”> </span>

    <span style=”color: #000000; font-family: Calibri;”>I can only have one active relationship between two tables and so I can’t have both of the date fields mentioned actively linked to my dates table. I’ve seen in the book (Power Pivot and Power BI: The Excel User’s Guide to DAX Power Query, Power BI & Power Pivot in Excel 2010-2016) that a solution is to join the date table to the data table more than once and then use USERELATIONSHIP in a CALCULATE expression to select different relationships for different measures. I have also seen whilst Googling mention of using two dates tables as a possible solution, although I have not yet found any detail on how this would work. A couple of questions:</span><span style=”color: #000000; font-family: Calibri;”> </span>

    <span style=”color: #000000; font-family: Calibri;”>Is using the USERELATIONSHIPS function to switch between relationships inefficient? I have no idea why but I instinctively feel that it would be costlier than having two dates tables.</span>

    <span style=”color: #000000; font-family: Calibri;”>If I had two dates tables how could I get the time slicing to work? If I want to see, say, counts for June 2015, would I have to separately filter each date table for June 2015? That might be OK for returning a single row but how would that work if I had the month/year field as a row heading?</span>

    #4457

    mikechina
    Participant
    • Started: 12
    • Replies: 38
    • Total: 50

    I always use the userelationship function in the situation I described.  I haven’t found any performance issues.  I also hate multiple calendar tables so I’m biased!

    #4464

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2552
    • Total: 2559

    I agree with Mike.

    #4518

    Spartacus
    Participant
    • Started: 3
    • Replies: 15
    • Total: 18

    Hi there,

    I’ve got a slightly different problem, but it’s related and so I do a “USERELATIONSHIP” 😉
    1. A fact table of “Incidents” with two (or more) date columns
    2. A date table with inactive relations from each of the fact table’s date columns to the date key

    Now I can of course easily set up measures like:

    Opened:=CALCULATE(COUNT([Created Time]);USERELATIONSHIP(Incidents[Created Time];Calendar[Date]))
    and
    Closed:=CALCULATE(COUNT([End Time]);USERELATIONSHIP(Incidents[End Time];Calendar[Date]))

    Those two formulas both work fine
    But with the count of the active Incidents, I get some weird results and cannot track down the error.
    This is my current formula:
    Active:=CALCULATE(COUNTROWS(Incidents);
    FILTER(Incidents; AND([Created Time] <= ENDOFMONTH(LASTDATE(Calendar[Date]))
    ;OR( [End Time]>= ENDOFMONTH(FIRSTDATE(Calendar[Date]));ISBLANK([End Time])))
    ))

    In the following row of example data, it gives me strange results:
    1. opened in the first 6 months: 183, 303, 266, 327, 343, 243
    2. closed in the first 6 months: 0, 0, 0, 0, 1375, 254
    This should result in a active incidents of 183, 486, 752, 1079, 47, 36, but instead, the final two numbers of my current calculation are 59 and 49.

    I guess it has something to do with a missing “USERELATIONSHIP”, but every version I tried (appending one or two USERELATIONSHIPS to the calculate formula, after the filter) , event gets worse.

    Any suggestions how to get a correct solution?

    Regards

    Olaf

    #4525

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2552
    • Total: 2559

    Interesting.

    If you do have another relationship between incidents and calendar that is also the active relationship, that “other” relationship would be governing the results.

    Do you have some sample anonymized data that could be attached?

    Tom

    #4526

    Spartacus
    Participant
    • Started: 3
    • Replies: 15
    • Total: 18

    Hi Tom,

    yep, interesting 😉
    Attached please find my current anonymized workbook for all those DAXperiments.

    A sidenote: Although my data currently is plain Excel, I still use Power Query to push them forward to PowerPivot.
    That’s mainly because the original data won’t be plain excel. I guess, that for the scope of this problem, this won’t matter.

    Regards

    Olaf

    Attachments:
    You must be logged in to view attached files.
    #4530

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2552
    • Total: 2559

    Olaf,

    Your formula played out very nicely in my Excel 2016 environment.

    What happens when you try something like this:

    Active 2 :=CALCULATE (
    COUNTROWS ( tab_Incidents ),
    FILTER (
    ALL ( tab_Incidents ),
    AND (
    [Created] < = MAX ( Calendar[Date] ), OR ( [End] >= MAX ( Calendar[Date] ), ISBLANK ( [End] ) )
    )
    )
    )

    Any better, any worse, the same?

    Tom

    #4531

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2552
    • Total: 2559

    Olaf,

    This is what I should have put up:

    Active Incident Count:=CALCULATE (
    COUNTROWS ( tab_Incidents ),
    FILTER (
    ALL ( tab_Incidents ),
    AND (
    [Created] < = MAX ( Calendar[Date] ), OR ( [End] >= MIN ( Calendar[Date] ), ISBLANK ( [End] ) )
    )
    )
    )

    Tom

    #4532

    Spartacus
    Participant
    • Started: 3
    • Replies: 15
    • Total: 18

    Hi Tom,

    that´s what I thought at first, too: Looks quite nice.
    But if you mind taking a look at the first year (2012), months 10 and 11, you see
    other values than the expected ones.
    In the new version, I´ve integrated your suggested measure ‘Active 2’ and a manual
    calculation of the expected values (with a simple formula in the Active-row).
    The difference is marked in orange.

    What about this?

    Regards

    Olaf

    Attachments:
    You must be logged in to view attached files.
    #4548

    Spartacus
    Participant
    • Started: 3
    • Replies: 15
    • Total: 18

    GOT IT 😉

    After drilling down some of the date which were counted and should not have been, I found the reason:

    In the Active-Measure, the End has to be GREATER THAN the lastdate of the month, not “GREATER OR EQUAL”.
    After Changing my measure to
    Active:=CALCULATE( COUNTROWS(tab_Incidents);
    FILTER( tab_Incidents;
    AND( [Created] <= LASTDATE(ENDOFMONTH(Calendar[Date])) ;
    OR( [End]> LASTDATE(ENDOFMONTH(Calendar[Date]));
    ISBLANK([End])))))
    everything seems to be fine.
    The difference is one “=” less than in the original spec.
    Now I can continue 😉

    Regards and thanks anyway for your time spent with this.

    Olaf

    #4557

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2552
    • Total: 2559

    Olaf,

    Pleased to see you were able to figure it out.

    Tom

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic.