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.
