Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Distinct count based on the sum and month

Hi Team, I have a table consisting column header like given below. I am looking for distinct count of vehicle driven less than 10km from last 3 months.  I tried the DAX expression but not getting th...
Show and Tell
v-zhenbw-msft's avatar
v-zhenbw-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ,

 

We can create a measure to meet your requirement.

 

Measure = 
VAR selected_month =
    DATE ( YEAR ( MAX('Table'[Date]) ), MONTH ( MAX('Table'[Date]) ) - 3, DAY ( MAX('Table'[Date]) ) )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Vehicle] ),
        'Table'[Date] >= selected_month,
        FILTER (
            SUMMARIZE (
                'Table',
                'Table'[Vehicle],
                "test", CALCULATE ( SUM ( 'Table'[Km Driven] ) )
            ),
            [test] < 10
        )
)

 

Then we can use a card visual and a line chart visual to show the result.

 

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ zhenbw

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