Last Total Value by Group

DAX DAX Calculations
1

Hi,

I’m working with a tabular model report and I need to create a DAX calculation which finds the last date value of miles for individual vehicles in the month, and then sums them together according to their fleet type. Dummy Data is in PBIX at bottom of my post.

image

So, as above, Truck 1 in Fleet Type Group A has completed 456 miles by the end of January and it will have completed 809 miles by the end of February.

So I need to sum up the Total Miles for each fleet type at the end of each month. So as per below, I need to sum the last value of the month for Truck 1 & Truck 2 that are of Fleet Type Group A. So total fleet miles for Group A would be the last value in the month for Truck 1 & Truck 2 (Truck 1 456 MIles + Truck 2 210 MIles. 456 + 210 gives me Group A Total Miles for the month of 666 miles). As this is test data, I will have several dozen vehicles in each fleet type so a calculation for individual trucks won’t work, it needs to be done at a Fleet Level.

image

Hopefully I’ve made sense, which would be pretty good considering it is 10:30am on a Monday morning :rofl:

End of Month Miles.pbix (80.4 KB)

2

Hello @DavieJoe,

Thank You for posting your query onto the Forum.

In order to achieve the results based on the scenario that you’ve mentioned, we’ll follow a two step process -

1). Creating a Calculated Column which identifies Month & Year based on Fleet Date -

Month & Year = 
FORMAT( FleetData[Date] , "M-YY" )

2). Now, create a measure which achieves the results -

Last Total Value by Group = 
VAR _vTable = 
SUMMARIZE(
    FleetData ,
    FleetData[Date] , 
    FleetData[Equipment ID] , 
    FleetData[Fleet Type] ,
    FleetData[Miles] , 
    FleetData[Month & Year] )

VAR _FTable = 
FILTER(
    _vTable , 

    VAR _Max_Date_Each_Month = 
    CALCULATE( MAX( FleetData[Date] ) , 
        ALLEXCEPT( FleetData , FleetData[Month & Year] , FleetData[Equipment ID] ) )

    VAR _Results = 
    FleetData[Date] = _Max_Date_Each_Month

    RETURN
    _Results )

VAR _Results = 
SUMX(
    _FTable , 
    FleetData[Miles] )

RETURN
_Results

Below is the screenshot of the final results provided for the reference -

Final Results

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Important Note:- You can also add that same column via Power Query as well.

Thanks and Warm Regards,
Harsh

End of Month Miles - Harsh.pbix (87.5 KB)

3 Likes
3

@Harsh Thank you Harsh, I had a feeling you might be the first responder, I had been trying to create this myself but I couldn’t quite conceptualise how it would work.

I can see how you’ve constructed this and where I was going wrong.

I will test this out this afternoon.

As always, really appreciate your help.

David

1 Like
4

I’ve not been able to test this yet @Harsh, I’m sure it will work. Hopefully tag as the solution this afternoon or tomorrow.

Thanks again.

DJ

1 Like
5

Hello @DavieJoe

Just following up if the response from @Harsh help you solve your inquiry.
If it did, please mark his answer as the SOLUTION.

If not, can you let us know where you’re stuck and what additional assistance you need?

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

1 Like
6

Hi @jeanrose87 marked as solved now as I was only able to work on this scenario this morning, works perfectly. Thanks to @Harsh for his outstanding help as always.

1 Like