Count Values multiplied by a measure

[rachk]

Hi

I have tried mutiple ways of making this work. I have fields that are dates, so month and day they departed, month and day they arrived which is then calculated into number of days. I have then calculated how many people (Count of ID) on this date travelled. What I am attempting (and failing to do) is calculate the number of days by the Count of ID to end up with a total number of days travelled. The below is the snap of what is currently there. 

The last one I tried:

TotalImpact=

{COUNTROWS (ourformname)* Max (ourformname [Number of Days]}

Would LOVE any guidance on this one. 

[rachk]

I havent, I havent been around. I am trying to get the pbix someone asked for but dont even know how to do that to remove the identifable info. I have some data in excel but that doesnt show the number of days calculating the way it does - feel a bit over my head here. 

[v-menakakota]

Hi   @rachk ,

Once you try to figure out how to mask sensitive data, Please share the data with us we will try to look into the issue.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards, 
Menaka. 
Community Support Team.

[rachk]

Hi

Departure date ends up displayed with month and day as does return date. 

ID is per person - this on my table shows as Count of ID

Number of days if derived between the dates of Departure date and return date. 

I am seeking the total number of days travelled per line eg on 5th June, there were 4 people who travelled 1 night so the last column I need to add should read 5. ID will come up on my screen as 5 as it identifies 5 people travelled on that date for 1 night each. 

DepartureDate	ReturnDate	ID	Number of Days
25/05/2025 4:45:00 PM	28/05/2025 7:40:00 PM	8	3
14/04/2025 6:00:00 AM	14/04/2025 4:00:00 PM	9	1
14/04/2025 6:30:00 AM	14/04/2025 5:00:00 PM	10	1
8/04/2025 7:00:00 AM	8/04/2025 3:00:00 PM	11	1
8/04/2025 7:00:00 AM	8/04/2025 2:30:00 PM	12	1
20/06/2025 1:00:00 PM	21/06/2025 11:00:00 AM	13	1
16/03/2025 5:00:00 PM	20/03/2025 8:00:00 PM	14	4
25/05/2025 4:00:00 PM	28/05/2025 8:00:00 PM	15	3
1/05/2025 6:30:00 AM	2/05/2025 10:00:00 AM	18	1
28/05/2025 12:00:00 PM	30/05/2025 1:00:00 PM	21	2
20/06/2025 6:30:00 AM	20/06/2025 7:30:00 PM	22	1
15/05/2025 10:00:00 AM	17/05/2025 2:00:00 PM	23	2
5/06/2025 1:25:00 PM	6/06/2025 7:00:00 AM	24	1
5/06/2025 11:00:00 AM	9/06/2025 2:00:00 PM	25	4
5/06/2025 8:00:00 AM	9/06/2025 12:00:00 AM	26	3
5/06/2025 9:00:00 AM	6/06/2025 5:00:00 PM	28	1
5/06/2025 10:20:00 AM	6/06/2025 4:20:00 PM	29	1
5/06/2025 1:25:00 PM	6/06/2025 1:00:00 PM	30	1
14/05/2025 5:45:00 AM	14/05/2025 8:30:00 PM	31	1
14/05/2025 12:00:00 AM	14/05/2025 12:00:00 AM	32	1
5/06/2025 10:20:00 AM	6/06/2025 11:55:00 AM	33	1
18/05/2025 2:19:00 PM	24/05/2025 7:13:00 AM	34	5
15/05/2025 10:00:00 AM	17/05/2025 2:00:00 PM	35	2
27/05/2025 7:00:00 AM	28/05/2025 8:00:00 PM	36	1
19/06/2025 1:25:00 PM	20/06/2025 7:40:00 PM	37	1

[v-menakakota]

Hi  @rachk  ,

Can you try this measure once and check.

Total Days Travelled =

SUMX (

    SUMMARIZE (

        TravelData,

        TravelData[DepartureDate],

        TravelData[ReturnDate],

        "@People", COUNT(TravelData[ID]),

        "@Days",   AVERAGE(TravelData[Number of Days])

    ),

    [@People] * [@Days]

)
Please go through the pbix file.

If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.
Best Regards, 
Menaka. 
Community Support Team  

[rachk]

I think that is getting closer. The ID moved into A1 etc but we have it counting each person who travelled so it needs to count. Number of days is already a measure based on the difference between the dates. 

I dont know how you got the @People part, it makes an error when I enter it, even following the above. 

[rachk]

Also, the total days travelled would work, IF we could get the ID to calculate if there is more than 1 person on that particular date. 

[v-menakakota · Accepted solution]

Hi @rachk ,

Try to check with this:

Total Days Travelled =
SUMX (
SUMMARIZE (
TravelData,
TravelData[DepartureDate],
TravelData[ReturnDate],
"PeopleCount", COUNTROWS(TravelData),
"TripDays", MAX(TravelData[Number of Days]) 
),
[PeopleCount] * [TripDays]
)
Also please go through the updated pbix file.
If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.
Best Regards, 
Menaka. 
Community Support Team  

[rachk]

OK, we have lift off! I have it working but its viewed with .00 at the end...yes I am still way over my head so apologise for this. At this point if it stays here then thats ok but if I can get it to a whole number, that would be great!

[v-menakakota]

Hi @rachk ,

We really appreciate your efforts and for letting us know the update on the issue.
It can be done by changing the values decimal places to 0. Please go through the below screenshot.

I hope this helps to resolve your query.

Thank you.

[v-menakakota]

Hi @rachk ,

May I ask if you have resolved this issue? If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you. 
 

Thank you,

Community Member.

[v-menakakota]

Hi @rachk ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster. 

Thank you. 

[v-menakakota]

Hi  ,
Thanks for reaching out to the Microsoft fabric community forum.
 
Can you check with this measure once:

TotalPersonDaysGrouped =

SUMX (

    ADDCOLUMNS (

        SUMMARIZE (

            TravelData,

            TravelData[DepartureDate],

            TravelData[ReturnDate]

        ),

        "Days", MAXX (

            FILTER (

                TravelData,

                TravelData[DepartureDate] = EARLIER(TravelData[DepartureDate]) &&

                TravelData[ReturnDate] = EARLIER(TravelData[ReturnDate])

            ),

            TravelData[Number of Days]

        ),

        "PersonCount", CALCULATE (

            COUNTROWS (TravelData)

        )

    ),

    [Days] * [PersonCount]

)

If I misunderstand your needs or you still have problems on it, please feel free to let us know.   

Best Regards, 
Hammad. 
Community Support Team  

[rachk]

The "days" are already a calculation so doesnt pick it up

[v-menakakota]

Hi  @rachk  ,
Thanks for reaching out to the Microsoft fabric community forum.
 
Can you check with this measure once:

TotalPersonDaysGrouped =

SUMX (

    ADDCOLUMNS (

        SUMMARIZE (

            TravelData,

            TravelData[DepartureDate],

            TravelData[ReturnDate]

        ),

        "Days", MAXX (

            FILTER (

                TravelData,

                TravelData[DepartureDate] = EARLIER(TravelData[DepartureDate]) &&

                TravelData[ReturnDate] = EARLIER(TravelData[ReturnDate])

            ),

            TravelData[Number of Days]

        ),

        "PersonCount", CALCULATE (

            COUNTROWS (TravelData)

        )

    ),

    [Days] * [PersonCount]

)

If I misunderstand your needs or you still have problems on it, please feel free to let us know.   

Best Regards, 
Hammad. 
Community Support Team