Counting based on varying time periods

[gambleave]

Can I please seek the group’s advice for this project scenario, which goes beyond my current skillset... 

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”)

The same fact table contains fields to identify the client (InteractionHistory[Client Name]) and the date of the interaction (InteractionHistory[Activity Date])

Different client accounts have different evaluation periods that we are concerned with - some quarterly, some biannual, some are annual).  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. 

I would like to have measures to count the value of Distinct Sales during the current and previous evaluation periods. 

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.  

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)

Any idea how to set up something like this? 

Thank you!

[mangaus1111]

Hi @gambleave ,

try the time intelligence functions:

Current_Period_QTD = CALCULATE([Distinct Sales],

                                                         DATESQTD('Date'[Date])

                                                       )

Previous_Period_QTD = CALCULATE([Current_Period_QTD],

                                                           DATEADD(

                                                                           'Date'[Date], -1 ,QUARTER

                                                                          )

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

                                                          )

[gambleave]

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.