Forum Discussion

bb252's avatar
bb252
Frequent Visitor
9 years ago
Solved

Counts per day

Not sure if this is possible, or at least i can't wrap my head around how to do this. I have a table tracks equipment checkouts.

 

ID EquipmentName   Checkout  CheckIn

1   Equipment1          1/1/2017   1/5/2017

2   Equipment2          1/1/2017   1/3/2017

1   Equipment1          1/5/2017   1/7/2017

 

I would like to show the total counts per day. Is there anyway to do this with the data formatted in above manner?

ie .   1/1 - 1/3  = 2 per day

        1/3 - 1/7 = 1 per day

 

Thanks!

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi bb252,

     

    Based on my test, you should be able to follow steps below to get your expected result.

     

    1. Add an individual Calendar table if you don't have yet, and make sure there is no relationships between your fact table and the Calendar table.

    Date = CALENDARAUTO()

    2. Use the formula below to create a new measure(just replace 'Table1' with your real table name).

    measure = 
VAR minDate =
    MIN ( 'Date'[Date] )
VAR maxDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Table1[ID] ),
        FILTER (
            Table1,
            NOT ( Table1[Checkout] >= maxDate
                || Table1[CheckIn] <= minDate )
        )
    )

    3. Then your should be able to use Date[Date] column as a Slicer to get your expected result.

     

     

    Here is the sample pbix file for your reference. :smileyhappy:

     

    Regards

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    Create yourself a Calendar table. You do not need to build a relationship to your table. Use a variant of this formula:

     

    Column = CALCULATE(COUNT(Orders[OrderNo]),FILTER(RELATEDTABLE(Orders),Orders[StartDate]<='Calendar'[Date] && Orders[EndDate]>='Calendar'[Date]))
  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi bb252,

     

    Based on my test, you should be able to follow steps below to get your expected result.

     

    1. Add an individual Calendar table if you don't have yet, and make sure there is no relationships between your fact table and the Calendar table.

    Date = CALENDARAUTO()

    2. Use the formula below to create a new measure(just replace 'Table1' with your real table name).

    measure = 
VAR minDate =
    MIN ( 'Date'[Date] )
VAR maxDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Table1[ID] ),
        FILTER (
            Table1,
            NOT ( Table1[Checkout] >= maxDate
                || Table1[CheckIn] <= minDate )
        )
    )

    3. Then your should be able to use Date[Date] column as a Slicer to get your expected result.

     

     

    Here is the sample pbix file for your reference. :smileyhappy:

     

    Regards