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 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.