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 ...
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())
            )
)
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())
            )
)