cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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])

RETURN
DISTINCT(vDates)
...
Mileage = SUMX('FACT Mileage', 'FACT Mileage'[Miles])

...

Mileage (PY) =
VAR zDate = SELECTEDVALUE('DIM Date'[Activity Dates])
VAR zYear = YEAR(zDate)
RETURN
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))

RETURN
IF(HASONEVALUE('DIM Vehicle'[DriverID]),
LessThan9000,
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.

2 REPLIES 2
Highlighted
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).


Regards

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





Highlighted
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

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

secondImage

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