Counting based on varying time periods

Question

Can I please seek the group’s advice for this project scenario, which goes beyond my current skillset...&nbsp;&nbsp;I have a measure to count the number of distinct sales from a fact table:Distinct Sales = CALCULATE(DISTINCTCOUNT(InteractionHistory[Activity ID]), InteractionHistory[Interaction Type] = “Sale”)&nbsp;The same fact table contains fields to identify the client (InteractionHistory[Client Name]) and the date of the interaction (InteractionHistory[Activity Date])&nbsp;Different client accounts have different evaluation periods that we are concerned with - some quarterly, some biannual, some are annual).&nbsp; Let’s say that I create a field in another table to indicate the period: ClientGroup[Evaluation Period], which is linked to the fact table through ClientGroup[Client Name]. There is also a standard date table in the model.&nbsp;&nbsp;I would like to have measures to count the value of Distinct Sales during the current and previous evaluation periods.&nbsp;For example, if it is now Oct 18, the current period for a quarterly client account would be from Oct 1 - Dec 31 and the previous period would have been from July 1 - Sep 30. &nbsp;If it is an annual account, the current period would be from Jan 1 year-to-date and if biannual from July 1 (previous period would be Jan 1 - June 30)&nbsp;Any idea how to set up something like this?&nbsp;&nbsp;Thank you!

mangaus1111 · Answer

Hi&nbsp;gambleave&nbsp;,&nbsp;try the time intelligence functions:&nbsp;Current_Period_QTD = CALCULATE([Distinct Sales],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DATESQTD('Date'[Date])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;)Previous_Period_QTD = CALCULATE([Current_Period_QTD],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DATEADD(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'Date'[Date], -1 ,QUARTER&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )

gambleave · Answer

Thanks for the feedback, but how can I incorporate the functionality to calculate the current and previous period based on whether it is quarterly, biannual or annual for. a particular account?

I want to use this to show as two columns in a dashboard with ~30 accounts that have varying evaluation periods.

Forum Discussion

Counting based on varying time periods

2 Replies

Related content

Summation of 25% intervals from data with varying counts

Product ids of varying length

Counting nulls

Showing 13 periods

Isinscope - varying distinctcount