Calculating a Measure over Time

Question

Hi,I am trying to create a series of calculation where I have a report date (say 10/1) and am trying to show the days since creation and days since last verified.&nbsp;My problem is that I need this calculation to work for each reporitng month (8/1,9/1, 10/1) and I want to use measures as opposed to creating columns to capture the calculation vs each reporitng month.What is the best way to go about doing this?My data is laid out this and I want to show similar scores both as a snapshot in time and on a trend.&nbsp;Any ideas would be greatly appreciated&nbsp;Project&nbsp; Created&nbsp; &nbsp; &nbsp;Last Verified&nbsp; &nbsp; Days Since Created&nbsp; &nbsp;Days Since Verified&nbsp; &nbsp;StalenessA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5/1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6/1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 150&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 120&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;=(150+120)/2=135&nbsp;&nbsp;

v-jingzhang · Answer

Hi&nbsp;Anonymous&nbsp;
&nbsp;
If I understand it correctly, you can try these measures:
&nbsp;
Days Since Created = 
VAR monthStart = MIN('Date'[Date])
RETURN
SUMX('Table',DATEDIFF('Table'[Created],monthStart,DAY))Days Since Verified = 
VAR monthStart = MIN('Date'[Date])
RETURN
SUMX('Table',DATEDIFF('Table'[Last Verified],monthStart,DAY))
&nbsp;

I have a Date table in the demo. You can download the attachment at bottom to see details.&nbsp;
&nbsp;
Best Regards,Community Support Team _ JingIf this post helps, please Accept it as Solution to help other members find it.

anonymous · Answer

This is awesome thanks!&nbsp;To further complicate things, I need to calculate a percent stale over time vs a disconnected date table.&nbsp; Stale would be greater than 30 days&nbsp;Percent stale would be the number stale/divide by the count of active and unverified applicaitons.&nbsp;I am trying to do this with a pretty complicated measure and I am getting zero percent and I am scratching my head.&nbsp; &nbsp;Do you have a change to this formula or a way to do this cleaner?Thanks&nbsp;&nbsp;CIIDCreated DateReport TypeDays Since Created15/1/2022Total Active Applications18028/1/2022Total Active Applications90310/1/2022Unverified Applications30411/1/2022Unverified Applications0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;At 11/1&nbsp;&nbsp;&nbsp;&nbsp;3 Applications stale 30 Days (2 active, 1 unverified)&nbsp;&nbsp;&nbsp;At 10/1&nbsp;&nbsp;&nbsp;2 Applications Stale&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Stale = greater than 30 days&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Percent Stale = Stale/ (Count of Active and Unverified Applications)&nbsp;&nbsp;&nbsp;At 11/1&nbsp;&nbsp;&nbsp;3/4=75%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;At 10/1&nbsp;&nbsp;&nbsp;3/3=100%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CUMULATIVE_STALENESS = var _filterdate1=max('FILTER DATES'[EOM])var stale_count=calculate(DISTINCTCOUNT('MASTER REPORTING QUERY'[CI ID]),'MASTER REPORTING QUERY'[Reporting Type]="Unverified",'MASTER REPORTING QUERY'[Adjusted Created Date]-_filterdate1&gt;=30)var _countact=calculate(DISTINCTCOUNT('MASTER REPORTING QUERY'[CI ID]),'MASTER REPORTING QUERY'[Reporting Type]="Total Active Applications")var _unver=calculate(DISTINCTCOUNT('MASTER REPORTING QUERY'[CI ID]),'MASTER REPORTING QUERY'[Reporting Type]="Unverified")var _filterdate=max('FILTER DATES'[CalendarDate])var _distinctcount_active_unver=_unver+_countactvar filter_count=calculate(_distinctcount_active_unver,'MASTER REPORTING QUERY'[Adjusted Created Date]&lt;=_filterdate&gt;=30)Returndivide(stale_count,_distinctcount_active_unver,0)&nbsp;&nbsp;&nbsp;

anonymous · Answer

Hi,I have another dilemma related to this same thing.&nbsp;I need to be able to filter the entire dashboard by a month end date.&nbsp; My problem is that these calculations are based off a disconnected table but I still need fitler all the data by a month.For example, select "Sepetember 22" from the main calendar and have everything (including this great calculation) and have it show only that month.&nbsp;How do I use a disconnected calendar table and a connected table in conjunction with one another.&nbsp;

Forum Discussion

Calculating a Measure over Time

3 Replies

Related content

DAX Calculated Measure

DAX Measure for Ranking the valid rows and calculating the average rating

Measure or Calculated Column

Efficiently scaling YoY calculations for hundreds of measures (Calculation Groups vs. Alternatives)

Measure to calculate

CIID	Created Date	Report Type	Days Since Created
1	5/1/2022	Total Active Applications	180
2	8/1/2022	Total Active Applications	90
3	10/1/2022	Unverified Applications	30
4	11/1/2022	Unverified Applications	0





		At 11/1
		3 Applications stale 30 Days (2 active, 1 unverified)
		At 10/1
		2 Applications Stale


		Stale = greater than 30 days

		Percent Stale = Stale/ (Count of Active and Unverified Applications)
		At 11/1
		3/4=75%

		At 10/1
		3/3=100%