Forum Discussion

Kevie's avatar
Kevie
Regular Visitor
2 years ago
Solved

How to divide by two different date groups

Hello,

I am new to the forum and will do my best to try and convey my current issue. This is related to finding the number of claims in a given month divided by the total number of items that were sold prior to that month PLUS if they are still in warranty. The whole idea is to find out what the ratio is of defective items compared to what is in warranty at that time.

 

For example:

 

Numerator - 5 claims in January 2022

Denominator - 500 items that have been sold up to January 2022 AND they are still in warranty

So the ratio would be 1% for that month.

 

The claims are coming from Table1 and the # of items sold and in warranty are from Table2. The main issue I am running into is that the dates are entirely different from each other. This is what it looks like in Excel currently for the denominator.

 

 

Please let me know if you need any additional information.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Kevie ,

    Maybe you need a new table like this:


    Then use these DAXs to create new measures:

    before and after = 
COUNTX (
    FILTER (
        Warranty,
        Warranty[InstallDate] <= SELECTEDVALUE ( 'Table'[Month of Claim] )
            && Warranty[WarrantyExpDate] >= SELECTEDVALUE ( 'Table'[Month of Claim] )
    ),
    Warranty[ItemID]
)
    of Claim = 
COUNTX (
    FILTER (
        Claim,
        YEAR ( Claim[ClaimDate] ) = YEAR ( SELECTEDVALUE ( 'Table'[Month of Claim] ) )
            && MONTH ( Claim[ClaimDate] ) = MONTH ( SELECTEDVALUE ( 'Table'[Month of Claim] ) )
    ),
    Claim[ClaimID]
)
    Rate = DIVIDE([of Claim], [before and after])

    The final output is below:

    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    Kevie , You need to have a common date table. And measure will be direct join, TI formula, or a between-date measure. You have to use the period from the date table in visual, measure, and slicers


    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    These can help

     

    Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM

    Employees as on Date : https://youtu.be/e6Y-l_JtCq4
    https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
    Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

     

    • Kevie's avatar
      Kevie
      Regular Visitor
      2 years ago

      I'm not sure where to upload the sample file so I attached the screenshots. I did this in Excel so it just needs to be translated into the Power BI equivalent.

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Kevie ,

        Maybe you need a new table like this:


        Then use these DAXs to create new measures:

        before and after = 
COUNTX (
    FILTER (
        Warranty,
        Warranty[InstallDate] <= SELECTEDVALUE ( 'Table'[Month of Claim] )
            && Warranty[WarrantyExpDate] >= SELECTEDVALUE ( 'Table'[Month of Claim] )
    ),
    Warranty[ItemID]
)
        of Claim = 
COUNTX (
    FILTER (
        Claim,
        YEAR ( Claim[ClaimDate] ) = YEAR ( SELECTEDVALUE ( 'Table'[Month of Claim] ) )
            && MONTH ( Claim[ClaimDate] ) = MONTH ( SELECTEDVALUE ( 'Table'[Month of Claim] ) )
    ),
    Claim[ClaimID]
)
        Rate = DIVIDE([of Claim], [before and after])

        The final output is below:

        Best Regards,
        Dino Tao
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.