Forum Discussion
Distinct count based on the sum and month
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
Community ChampionHi Anonymous
Can you add a table with the expected result?
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
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
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.