Results 1 to 5 of 5

Thread: Question Re: Time Intelligence Functions
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2013
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Question Re: Time Intelligence Functions

    Hi,

    I'm new to Powerpivot and working my way through Rob Collie's DAX book. I've just gone through the Time Intelligence chapter and it's prompted me to look at some data I have at work.

    I have an insurance claim transaction database and I want to count how many claims there are where some payment has been made, at various points in time. A claim "transactions" do not necessarily involve any payments, but, for example, could simply be logging a claim notice in the system.



    I was able to count the number of unique claims by a certain date, irrespective of payments, by using the following:


    [Num Claims] = DISTINCTCOUNT(transactions[ClaimID]) and
    [Num Claims To Date] =
    CALCULATE([Num Claims],
    DATESBETWEEN(calendar[Date],
    FIRSTDATE(ALL(calendar[Date])),
    LASTDATE(calendar[Date])
    )
    )



    I've also set up a cumulative paid measure as


    [Paid To Date] =
    CALCULATE(transactions[Payment],
    DATESBETWEEN(calendar[Date],
    FIRSTDATE(ALL(calendar[Date])),
    LASTDATE(calendar[Date])
    )
    )



    Is it possible to set up a measure to get the number of claims with at least one payment at given points in time? i.e., cumulative number of unique ClaimIDs where [Paid To Date] > 0 at, say, each month end?




    Many thanks,


    Bruno

  2. #2
    Board Regular
    Join Date
    Dec 2012
    Posts
    294
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Question Re: Time Intelligence Functions

    if it makes sense then its possible, Bruno! =)
    You might want to change that DATESBETWEEN inside the Paid to Date measure to something like DATESBETWEEN( calendar[date], firstdate that you choose in the slicers, lastdate that you choose in the slicers) and that should give you the paid amount in an specific date range...then you can do something more fancy like
    IF( AND( [Paid to Date] >0, [the new measure that we talked about above]>0 ), [Num of Claims], blank() )

    If I didn't understand the question then what I said above will not work but let me know if it does!

  3. #3
    New Member
    Join Date
    Aug 2013
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Question Re: Time Intelligence Functions

    Thanks Miguel - I'm very grateful for you reply. I don't think I was 100% clear with my question, although your reply did get me to try a few more things. I think I'm getting closer!

    To make it clearer, here's a small data set to illustrate what I tried.

    ClaimID Trans Date Payment
    1 1-Jan-10 0.00
    1 1-Mar-11 0.00
    1 1-Dec-12 10.00
    1 1-Feb-13 10.00
    2 1-Jan-12 0.00
    2 1-Mar-12 0.00
    3 1-Feb-11 10.00
    4 1-May-12 0.00
    4 1-Jan-13 5.00
    5 1-Jan-11 0.00
    5 1-Jan-12 10.00
    5 1-Jan-13 -10.00
    and a Calendar table:

    Transaction Date Transaction Year
    1-Jan-10 2010
    2-Jan-10 2010
    3-Jan-10 2010
    ...
    I've tried the following measure and added ClaimID in the row label to help me understand what's going on, although I'm only interested in the totals:

    [Num Claims with Payments To Date Method 1] =
    CALCULATE([Num Claims to Date], DATESBETWEEN(Calendar[Date], FIRSTDATE(ALL(Calendar[Date])), LASTDATE(Calendar[Date])), Transactions[Payment]>0)

    Which give me the following (with Year from the Calendar table as a row heading)

    Num Claims with Payments To Date Method 1 Column Labels
    Row Labels 2011 2012 2013
    1 1 1
    3 1 1 1
    4 1
    5 1 1
    Grand Total 1 3 4

    This is almost what I want - the only issue is that I'd like to get rid of ClaimID=5 in 2013 as the total paid is 0 because of the -10 payment 1/1/2013. ie, I'm picking up claims that had any payment>0 up to the row label date, instead of claims that have cumulative paid > 0 at the row label date.

    I've also used your suggestion:

    [Num Claims with Payments To Date Method 2] = if([Paid to Date]>0,[Num Claims to Date],BLANK())

    Which gives me:

    Num Claims to Date With Payments Method 2 Column Labels
    Row Labels 2011 2012 2013
    1 1 1
    3 1 1 1
    4 1
    5 1
    Grand Total 3 5 5
    The problem I get with this measure is that it works for an individual ClaimID, but the totals don't work as I need.

    Here's what I really would like to get:

    Column Labels
    2010 2011 2012 2013
    What I really want 0 1 3 3

    Any further suggestions?

  4. #4
    Board Regular
    Join Date
    Dec 2012
    Posts
    294
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Question Re: Time Intelligence Functions

    can you send me a copy of your workbook via dropbox or skydrive so I can take a look? I think I'm clear now about what you want

  5. #5
    New Member
    Join Date
    Aug 2013
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Question Re: Time Intelligence Functions

    In case someone gets this far... Miguel (thanks!) was able to solve this problem for me offline.

    The solution was to

    1) create a new table called 'Claims' with unique claim id/numbers

    ClaimID
    1
    2
    3
    4
    5

    2) Create a new relationship where this new table is a lookup table for ClaimID in the Transactions table.

    3) Define a new measure =COUNTX( FILTER(Claims, [Paid to Date]>0),Claims[ClaimID])

    Cheers,

    Bruno

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •