Showing results for 
Search instead for 
Did you mean: 
Helper I
Helper I

I'm trying to 'LessThan' variable to calculate mileage

I'm trying to calculate 'Less Than' a defined mileage drived by drivers for the prior year. The formula is based on "mileage", "driver id" and "prior year". I have a measure for "prior year" which works since it gives the prior year mileage for each driver id.
I created a dax formula that will give the subtotal of who are less than the required mileage for the prior year and that's where it's not giving the right computation. I'm sharing the measures that I have in place so that you understand how they interact.
I keep a couple dimension tables for the Activity Dates, Driver info. I also keep a couple FACT tables for the Miles, Mileage Date, and Activity Dates. My problem is the measure for under9000 returns the correct number when January month is selected. If I select Feb, March, Apr, May... the total is not correct.

DIM Dates =
VAR vDates = UNION(SUMMARIZE('DIM Vehicle', 'DIM Vehicle'[Activity Dates]), SUMMARIZE('FACT Mileage','FACT Mileage'[Mileage Date])

Mileage = SUMX('FACT Mileage', 'FACT Mileage'[Miles])


Mileage (PY) =
VAR zDate = SELECTEDVALUE('DIM Date'[Activity Dates])
VAR zYear = YEAR(zDate)
CALCULATE([Mileage], ALL('DIM Date'[Activity Dates]), YEAR('FACT Mileage'[Mileage Date]) = zYear-1)

under9000miles =
VAR zDate = SELECTEDVALUE('DIM Vehicle'[Activity Dates])
VAR zYear = YEAR(zDate)
VAR LessThan9000 = IF([Mileage (PY)] <= 8999,1,0)
VAR LessTable = SUMMARIZE('DIM Vehicle','DIM Vehicle'[DriverID],"Less Than or Equal to 8999",
IF(CALCULATE([Mileage (PY)], 'DIM Vehicle'[DriverID] = 'DIM Vehicle'[DriverID]) <= 8999,1,0))

IF(HASONEVALUE('DIM Vehicle'[DriverID]),
SUMX(LessTable, [Less Than or Equal to 8999]))

To test, here are the key tables needed
DIM Dates (this is the measure that takes into account all the Activity Dates and Mileage Date, see the formula I shared above) **the mileage dates must be 1/1/2019 to 12/31/2020**
DIM Driver table that has driver info: Driver Name, DriverID, Driver State
DIM Vehicle table that has driver info: Activity Dates 1/1/2019 to 12/31/2020, Driver Name, DriverID,
FACT table that has Miles, DriverID, Mileage Date that is 1/1/2019 to 12/31/2020

I really need help with this as I'm at a loss why i can't get the correct numbers to show in the table for the total of drivers who are under 9000 miles. I hope the above is helpful and appreciate any help and hoping to get this to work.

Super User V
Super User V

Re: I'm trying to 'LessThan' variable to calculate mileage

Hi @jamaicawi ,


Can you plewase submit some data sample just giving the tables columns names makes it difficult to make a mockup.


If you can share the PBIX file and expected result would be even better, or at least some mockup, if there is sensitive information you can do it trough private message.


Please see this post regarding How to provide sample data in the Power BI Forum (courtesy of @ImkeF).


Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português

Helper I
Helper I

Re: I'm trying to 'LessThan' variable to calculate mileage

@MFelix, yes i will provide samples and thank you!

Helpful resources

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.


Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors