Distinct count of Serial number if falls within the date range

[PBI_newuser]

Hi, 

How to calculate the distinct serial numbers that having start date and end date falling between the selected created date range?

For example: Distinct count of serial number = 6 - 2 (out of the range) = 4

[V-lianl-msft]

Hi @PBI_newuser ,

What do you mean? Has the previous problem been solved? Or do you mean the new problem?

If it is a new problem, could you describe more information to let me know your scenario

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

[PBI_newuser]

Hi @V-lianl-msft,

Yea, the previous problem is solved.

How can I modified the below measure by using the Datesbetween logic?

SN Count Exist in Table B =
VAR t =
        FILTER (
        'Product',
        'Product'[Start Date] <= MIN ( 'Table'[Date] )
            && 'IProduct'[End Date] >= MAX ( 'Table'[BD Notified Date])
    )
RETURN
     CALCULATE(DISTINCTCOUNT('Product'[Serial Number]),FILTER (t, [Serial Number] IN VALUES ( 'Table B'[Serial Number] ) ) )

[V-lianl-msft]

Hi @PBI_newuser ,

---

Based on the formula previously provided .

Try to use if statement or add filter condition :

IF(MAX('Table A'[Serial Number]) IN VALUES ( 'Table B'[Serial Number] ) ,XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX)

OR

xxxxxxxxxxxxxxxx,FILTER(Table A,'Table A'[Serial Number] IN VALUES ( 'Table B'[Serial Number] ))

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

[PBI_newuser]

Thanks @V-lianl-msft !

As advised, I have created the calendar table and create relationship with the [Created Date] in 'Table'.

Why I can't create the "period" by using [Created Date] anymore?
The calendar logo is disappeared beside the [Created Date] field.

Period = 'Table'[Created Date].[Year] & "-" & FORMAT('Table'[Created Date].[Date],"MM") 

[V-lianl-msft]

Hi @PBI_newuser ,

Actually, when you create the relationship (Many to one or one to Many) between the tables with Date, only the date in the one side will show date hierarchy. 

Try:

period = FORMAT('Table (2)'[date],"YYYY-MM")

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

[amitchandak]

@PBI_newuser , check if the attached file can help

[PBI_newuser]

Hi @amitchandak ,

If year 2020 is selected, the count should be 4. (a,b,c,d start & end date touch the selected month)

If year 2020 month June is selected, the count should be 2.

How to modify the logic?

Count serial number if the range of start and end date touch selected range of "Created Date".

Thanks.

[V-lianl-msft · Accepted solution]

Hi @PBI_newuser ,

Try to create a measure like this:

Measure = 
CALCULATE (
    DISTINCTCOUNT ('Table'[id] ),
    GENERATE (
        VALUES ( 'Table 2'[Date] ),
        FILTER (
            'Table',
            CONTAINS (
                DATESBETWEEN (
                    'Table 2'[Date],
                    'Table'[Created date],
                    'Table'[Closed date]
                ),
                'Table 2'[Date],'Table 2'[Date]
            )
        )
    )
)

  Sample .pbix 

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

[PBI_newuser]

Hi @V-lianl-msft,

I have tried the below measure but it shows the below error.

May you assist why this happened?

Measure =
CALCULATE (
    DISTINCTCOUNT ('Product'[Serial Number]),
    GENERATE (
        VALUES ('Table'[Created Date]),
        FILTER (
            'Product',
            CONTAINS (
                DATESBETWEEN (
                   'Table'[Created Date],
                    'Product'[Start Date],
                    'Product'[End Date]
               ),
              'Table'[Created Date],'Table'[Created Date]
            )
        )
    )
)

[V-lianl-msft]

Hi @PBI_newuser ,

Try to create a calendar table and create relationships

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

[PBI_newuser]

Hi @V-lianl-msft ,
How can I have one more measure to exclude those Serial Number exist in the other table with the same logic?