DAX measures when there are two date columns

[Sandra_James]

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

1	3/1/2021	Internal Defect	3/15/2021	Germany
2	3/1/2021	Internal Defect	3/1/2021	Germany
3	3/1/2021	Accurate		Germany
4	3/1/2021	Accurate		Germany
5	3/5/2021	Accurate		Germany
6	3/2/2021	External Defect	3/2/2021	Germany
7	3/2/2021	Accurate		Germany
8	3/2/2021	Internal Defect	3/3/2021	Germany
9	3/2/2021	Internal Defect	3/22/2021	Germany
10	3/5/2021	Internal Defect	3/5/2021	Germany
11	3/1/2021	External Defect	3/5/2021	Germany
12	3/1/2021	Internal Defect	3/2/2021	Germany
13	3/1/2021	External Defect	3/5/2021	Germany

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

[lbendlin]

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. 

[Sandra_James]

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?

[v-rzhou-msft]

Hi @Sandra_James ,

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.

[Sandra_James · Accepted solution]

Thanks a lot!!
It's working perfectly 🙂