Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX measures when there are two date columns

Hi All,

 

I'm facing an issue in creating a measure using 2 date columns in the dataset.

Overview of the dataset:

Sales DocumentCreated OnDefect typeDefect dateCountry

13/1/2021Internal Defect3/15/2021Germany
23/1/2021Internal Defect3/1/2021Germany
33/1/2021Accurate Germany
43/1/2021Accurate Germany
53/5/2021Accurate Germany
63/2/2021External Defect3/2/2021Germany
73/2/2021Accurate Germany
83/2/2021Internal Defect3/3/2021Germany
93/2/2021Internal Defect3/22/2021Germany
103/5/2021Internal Defect3/5/2021Germany
113/1/2021External Defect3/5/2021Germany
123/1/2021Internal Defect3/2/2021Germany
133/1/2021External Defect3/5/2021Germany

The count of sales document shows the orders proocessed on a particular date

The defect type column shows how the orders that turned out to be defective and orders that were Accurate.There are 2 date column - one which show the order creation date and another which shows the defect complaint log date.

I need to calculate 2 measure  - one which shows the defect % based on the order date(orders which turned out to be defects out of the orders processed based on order created date) and another which shows the defect % based on Defect log in date.

If I use the Defect filter date the defect % should be total defects/orders created on the defect date.(But when I filter , %comes us 100% .Is there a workaround for this issue as there are 2 date columns)
When I use the order created filter the defect % should be defect/orders created date(This measure is working fine for me)

 

Could anyone please help me on this?

 

Regards,

Sandra

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Thanks a lot!!
    It's working perfectly 🙂

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    Use a calendar table

    Connect the date from the calendar table to the "created on" date with an active connection

    Connect the date from the calendar table to the "defect date" with an inactive connection.

     

     

    Use the DAX function USERELATIONSHIP() in your measures to switch relationships on the fly.

     

     

     

     

    "If I use the Defect filter date the defect % should be total defects/orders created on the defect date".

    This is unclear. Please show the expected outcome. 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi,

      Thank you for reply.I'm able to get the defect % that you mentioned above using the measure that I have created.This gives me how many of the orders logged on 3/1/21 turned out out to be defective - 71.4%
      My real issue is when I select a date from defect type filter, it should give me the % of defect out of all the orders created on the selected defect date.
      Based on created on date - on 3rd March I had 7 orders and 5 were logged as defects

       

      Based on defect date filter :

      Is this kind of calculation possible?

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous ,

         

        According to your screenshot, I think "Created On" slicer and "Defect Date" slicer are both built by columns in your data table. When you select 3/1/2021 in your "Defect Date" slicer, Power BI will filter 3/1/2021 in [Defect Date] column, so you will get both 1.

        It is better for you to create an unrelated "Dim Defect Date" table to build the "Defect Date" slicer.

        Dim Defect Date = CALCULATETABLE( VALUES('Table'[Defect date]),'Table'[Defect date]<>BLANK())

        Then create measures to calcualte Defects and Orders Processed.

        Defects =
VAR _Select_DefectDate =
    SELECTEDVALUE ( 'Dim Defect Date'[Defect date] )
VAR _True =
    CALCULATE (
        COUNT ( 'Table'[Sales Document] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Defect type] <> "Accurate"
                && 'Table'[Defect date] = _Select_DefectDate
        )
    )
VAR _False =
    CALCULATE (
        COUNT ( 'Table'[Sales Document] ),
        FILTER ( 'Table', 'Table'[Defect type] <> "Accurate" )
    )
RETURN
    IF ( ISFILTERED ( 'Dim Defect Date'[Defect date] ), _True, _False )
        Orders Processed =
VAR _Select_DefectDate =
    SELECTEDVALUE ( 'Dim Defect Date'[Defect date] )
VAR _True =
    CALCULATE (
        COUNT ( 'Table'[Sales Document] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Created On] = _Select_DefectDate )
    )
VAR _False =
    CALCULATE ( COUNT ( 'Table'[Sales Document] ) )
RETURN
    IF ( ISFILTERED ( 'Dim Defect Date'[Defect date] ), _True, _False )
        Defect % by order date = 
DIVIDE([Defects],[Orders Processed])

        Result is as below.

         

        Best Regards,
        Rico Zhou

         

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