Forum Discussion
Subtracting Measure from Calculated Column
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 )
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
Result
That should work no matter what level of the date hierarchy you are at.
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 )