Forum Discussion
Solved
Subtracting Measure from Calculated Column
I have a table that lists out trucks and their individual operational dates. I want to distinctcount the Ops Dates and subtract that from the number of days per month from my calendar table to show ...
If you have a date table linked to your main table, you can create a measure like
Num Days Not Running = VAR WorkingDates = DISTINCT ( 'Table'[Start Date] ) VAR AllDates = VALUES ( 'Date'[Date] ) VAR Result = COUNTROWS ( EXCEPT ( AllDates, WorkingDates ) ) RETURN ResultThat should work no matter what level of the date hierarchy you are at.
You can wrap the whole lot in an iterator over the tractors, e.g.
Num Days Not Running = SUMX ( VALUES ( 'Table'[Tractor Number] ), VAR WorkingDates = DISTINCT ( 'Table'[Start Date] ) VAR AllDates = VALUES ( 'Date'[Date] ) VAR Result = COUNTROWS ( EXCEPT ( AllDates, WorkingDates ) ) RETURN Result )
John,
Thanks for your help. That solution worked when I pulled in the tractor number, but it doesn't work when I put the value in a card. When I put number of days not running in a car for a week period I only show 1 day not running. I know that is not correct. How can I get this to work where I can sum the number of days remaining regardless of the visual or data point that is pulled in? Thanks.
You can wrap the whole lot in an iterator over the tractors, e.g.
Num Days Not Running =
SUMX (
VALUES ( 'Table'[Tractor Number] ),
VAR WorkingDates =
DISTINCT ( 'Table'[Start Date] )
VAR AllDates =
VALUES ( 'Date'[Date] )
VAR Result =
COUNTROWS ( EXCEPT ( AllDates, WorkingDates ) )
RETURN
Result
)