Forum Discussion

tsinning's avatar
tsinning
New Member
3 years ago
Solved

Running Total

Hi,

 

What is the best way to get a running total of assets by month. My table has a "date created" and "date retired" column. I want to look at how many assets were in service each month. Here is an example of data

 

 

Data Modeling
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    I tried to simplify the sample like below.

    I suggest having a calendar table like below.

     

     

     

     

    Asset count measure: = 
CALCULATE( COUNTROWS(Data), 
    FILTER( Data, 
            Data[Created Date] <= MAX( 'Calendar'[Date]) && 
                OR( Data[Retired Date] >= MIN('Calendar'[Date]), Data[Retired Date] = BLANK())
            )
)

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    I tried to simplify the sample like below.

    I suggest having a calendar table like below.

     

     

     

     

    Asset count measure: = 
CALCULATE( COUNTROWS(Data), 
    FILTER( Data, 
            Data[Created Date] <= MAX( 'Calendar'[Date]) && 
                OR( Data[Retired Date] >= MIN('Calendar'[Date]), Data[Retired Date] = BLANK())
            )
)