Forum Discussion

cheid_4838's avatar
cheid_4838
Helper IV
1 year ago
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 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.

 

 

Need Help
  • johnt75's avatar
    johnt75
    1 year ago

    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's avatar
    johnt75
    1 year ago

    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
)

3 Replies

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    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's avatar
      cheid_4838
      Helper IV
      1 year ago

      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's avatar
        johnt75
        Super User
        1 year ago

        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
)