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 1 3/1/2021 Internal D...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Thanks a lot!!
    It's working perfectly 🙂

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.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks a lot!!
    It's working perfectly 🙂