Post reply

DAX Query Help Required

  • March 24, 2017 at 4:23 am

    #148046

    Hi

    I'm hoping someone can provide some assistance in relation creating a DAX Measure or in relation to my structure.

    Overview

    • A Customer is Approved Funding by the Company
    • At a later date that Funding if Offered to the Customer (once)
    • At later dates after the Offer the Customer draws down the funding (payments)

    My Core Measures are as follows:-

    • Approval Amt: CALCULATE(SUM('Fact Approval'[Approval Amount]))
    • Offer Amt: CALCULATE(SUM('Fact Offer'[Offer Amount]))
    • Paid Amt: CALCULATE(SUM('Fact Payment'[Payment Amount]))

    Which of course works fine when you drill down in a logical manner.

    The Problem
    However how can I drill up ie

    •     what is the Value of all Approvals for Payments made in Year 2017  (bear in mind multiple payments exists for a single Approval or Offer)
    •     what is the value of all Approval and associated Payments when Offer status is Pending (payment would be gotten but not Approval)

    Thanks

    My Dimensions are

    • Customer Data
    • Approval Data
    • Offer Data
    • Payment Data

    My 3 fact tables are

    Fact Approval

    • Customer Key
    • Approval Key
    • Approval Amount

    Fact Offer

    • Customer Key
    • Approval Key
    • Offer Key
    • Offer Amount

    Fact Payment

    • Customer Key
    • Approval Key
    • Offer Key
    • Payment Key
    • Payment Amount

  • March 28, 2017 at 8:40 pm

    #1935417

    what is the Value of all Approvals for Payments made in Year 2017

    ...Where is your Calendar table? Then you can do time-based summaries, like this:

    CALCULATE( SUM([Approval Amount]), Calendar[Year] = 2017 )

    You can also do running totals etc, like YTD calculations with them.

  • April 3, 2017 at 3:23 am

    #1936223

    @pietlinden, thank you for taking the time to reply.  Opppsss I have a calendar table, just forgot to mention it above.

    Thanks for the example code above.  Whilst this provides me with a total of Approvals for 2017, I still have the issue (I think) that I only want the Approval value for only those Offers offered in 2017.

    For example

    There are 10 Approvals in 2017 totaling €1,000
    Of the 10 Approvals only 2 were offered in 2017 (which were worth €100 each)

    If I filter on Offer Year (2017) I would be looking for a total of €200 as that is the total of the Approvals that was Offered in 2017.

    Hope the above makes sense.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply