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 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
MonthVehicle NumberKm Driven
Jan 2019A126
Dec 2019A126
Dec 2019A139.5
Jan 2020A1511
Feb 2020A1315
Oct 2019A116
Nov 2019A139
Nov 2019A1250
Oct 2019A1120
Jan 2020A1215
Show and Tell

6 Replies

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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

      MonthVehicle Km Driven
      Jan 2020A133
      Dec 2019A134 
      Oct 2019A1311
      Jan 2020A126
      Dec 2019A123
      Oct 2019A120.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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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 VehicleKm Driven
      Jan-201QJ5ES\CC3773\COLORADO3
      Jan-20(MMU145DK0LH608973) Holden Colorado4
      Jan-20(MMU148DK0LH605575) COLORADO5
      Jan-20(MMU147DK0LH623200) Holden Colorado2
      Jan-20(MMU147DK0LH623142) Holden Colorado2
      Jan-20(1DOC504) 44401 CAT Skid Steer5
      Jan-20(156RJQ) 44390 Franna Crane4
      Jan-20(1NP3WZ) 73894 Isuzu Tipper - Metro Quarry 13
      Jan-201MP2YD\CC2722\COLORADO4
      Jan-20574YKO\CC2946\COLORADO2
      Jan-20CP35SM\CC2497\COLORADO2
      Jan-201GRK225\CC3263\COLORADO3
      Jan-20(MMU148DK0LH605220) Holden Colorado3
      Mar-20(156RJQ) 44390 Franna Crane3
      Mar-20(1NT9IV) 73848 Hitachi ZX33U Excavator - Geelong2
      Mar-20(C86990) CAT Skid Steer 216B 44115/PTQ38904
      Mar-20(CB63XX) TT178 Hino Dual Cab2
      Mar-20142YPS\CC3128\COLORADO3
      Mar-201GTR840\90138\TRITON3
      Mar-201GTD407\90151\TRITON3
      Mar-20(1TTX599) Sullair 425H3
      Mar-20G70D20FF416D 9/01/20202
      Feb-20(BI56KQ) SU006Hino Street Sweeper2
      Feb-20(C86990) CAT Skid Steer 216B 44115/PTQ38905
      Feb-20(028SKH) SP030  Mitsubishi Fuso Cement Spreader2
      Feb-20XV20JT 73884 Isuzu Beaver Tail - TBA4
      Feb-20(1MT3MA) 73862 Isuzu Beaver Tail - At Almighty3
      Feb-201GLM991\CC2542\COLORADO3
      Feb-20LT017 ISUZU NPS SERVICE CREW2

       

      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's avatar
      Anonymous
      Not applicable
      6 years ago

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