Subtracting Measure from Calculated Column

[cheid_4838]

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 how many days per week, month, and year the trucks were not running.  I am not sure how to subtract a days per month column from the distinctcount of operational dates.  Is there anyway to create a measure of days per month?  Any help you can provide is appreciated.

[johnt75 · Accepted solution]

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.

[johnt75 · Accepted solution]

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.

[cheid_4838]

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.  

[johnt75 · Accepted solution]

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
)