Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gambleave
Helper II
Helper II

Counting based on varying time periods

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!
2 REPLIES 2
mangaus1111
Solution Sage
Solution Sage

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.

                                                          )

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. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.