Distinct count based on the sum and month

[Anonymous]

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 the correct answer =

CALCULATE(DISTINCTCOUNT(Table [Vehicle Number ]),FILTER(Table[Km Driven], Km Driven <10),DATESINPERIOD(Master_Data[Month ],MAX(Master_Data[Month ]),-3,MONTH))

Kindly help

Month	Vehicle Number	Km Driven
Jan 2019	A12	6
Dec 2019	A12	6
Dec 2019	A13	9.5
Jan 2020	A15	11
Feb 2020	A13	15
Oct 2019	A11	6
Nov 2019	A13	9
Nov 2019	A12	50
Oct 2019	A11	20
Jan 2020	A12	15

[v-zhenbw-msft]

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.

[amitchandak · Accepted solution]

@Anonymous , Try like

countx(filter(summarize(Table, Table[Vehicle Number], "_1",
CALCULATE(sum(Table[Km Driven]),DATESINPERIOD(Master_Data[Month ],MAX(Master_Data[Month ]),-3,MONTH))),[_1]<10),[Vehicle Number])

I am hosting a webinar on 25th April on Power BI, Check Details - https://www.linkedin.com/posts/amitchandak78_webinar-tech-techforgood-activity-6658266754378231808-ye5L

[Anonymous]

Hi,

Is it possible if I can aslo calculate the sum of km driven for those vehicles as well? I want to show the sum of km driven in last 3 month just next to the Overall Km driven of all vehicle in another column in table i.e Sum of km

I am trying the below DAX query but unable to fetch the results.

SUMx(filter(summarize(Master_Data, Master_Data[Item], "_1",CALCULATE(sum(Master_Data[Utilisation KM]),DATESINPERIOD(Master_Data[Month ],MAX(Master_Data[Month ]),-6,MONTH))),"_1"<10)

Kindly help

[Mariusz]

Hi @Anonymous 

Can you add a table with the expected result?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Hi All,

Result aree not accurate. Please find the below sample sheet again for your review.

Amit: I have previous year data in sheet as well, So I would like to perfrom the sum from starting and then want to filter last 3 month data. eg. Calculate sum of Km driven and then filter 3 month data and then calculate the distinct count.

Month	Vehicle	Km Driven
Jan-20	1QJ5ES\CC3773\COLORADO	3
Jan-20	(MMU145DK0LH608973) Holden Colorado	4
Jan-20	(MMU148DK0LH605575) COLORADO	5
Jan-20	(MMU147DK0LH623200) Holden Colorado	2
Jan-20	(MMU147DK0LH623142) Holden Colorado	2
Jan-20	(1DOC504) 44401 CAT Skid Steer	5
Jan-20	(156RJQ) 44390 Franna Crane	4
Jan-20	(1NP3WZ) 73894 Isuzu Tipper - Metro Quarry 1	3
Jan-20	1MP2YD\CC2722\COLORADO	4
Jan-20	574YKO\CC2946\COLORADO	2
Jan-20	CP35SM\CC2497\COLORADO	2
Jan-20	1GRK225\CC3263\COLORADO	3
Jan-20	(MMU148DK0LH605220) Holden Colorado	3
Mar-20	(156RJQ) 44390 Franna Crane	3
Mar-20	(1NT9IV) 73848 Hitachi ZX33U Excavator - Geelong	2
Mar-20	(C86990) CAT Skid Steer 216B 44115/PTQ3890	4
Mar-20	(CB63XX) TT178 Hino Dual Cab	2
Mar-20	142YPS\CC3128\COLORADO	3
Mar-20	1GTR840\90138\TRITON	3
Mar-20	1GTD407\90151\TRITON	3
Mar-20	(1TTX599) Sullair 425H	3
Mar-20	G70D20FF416D 9/01/2020	2
Feb-20	(BI56KQ) SU006Hino Street Sweeper	2
Feb-20	(C86990) CAT Skid Steer 216B 44115/PTQ3890	5
Feb-20	(028SKH) SP030  Mitsubishi Fuso Cement Spreader	2
Feb-20	XV20JT 73884 Isuzu Beaver Tail - TBA	4
Feb-20	(1MT3MA) 73862 Isuzu Beaver Tail - At Almighty	3
Feb-20	1GLM991\CC2542\COLORADO	3
Feb-20	LT017 ISUZU NPS SERVICE CREW	2

Need to perfrom sum first to check wether the vehicle has driven less than 10km and then filter data of 3 month less than 10km driven.

Kindly help me.

[Anonymous]

Hi,

Please check I have alreadt given a table consisting month, Vehicle number and Km driven in month. I want to sum up all the vehicle km driven and then to calculate the distinct count of number of vehicle for last three month.

For example

Month	Vehicle	Km Driven
Jan 2020	A13	3
Dec 2019	A13	4
Oct 2019	A13	11
Jan 2020	A12	6
Dec 2019	A12	3
Oct 2019	A12	0.5

I am looking for the sum of all vehicle km driven in last 3 months i.e A12 driven 9.5 km and A13 driven 19KM (hence need to exclude it from the data) and the answer of unique vehicle would be A12 only i.e 1