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
hrafnkel11
Helper I
Helper I

How to compare following quarter total with previous quarter target

Hi - I have a fact table for contact attributes that contains beginning of quarter snapshots plus today. This is used to aggregate contact totals at each snapshot time period. Example below aggregated for simplicity:

 

AS_OF_DATE

  TOTAL CONTACTS

1/1/2023

1

4/1/2023

1

4/13/2023

2

 

 

I also have a target table that shows target expectations for each quarter. These are end-of-quarter expectations.

 

TARGET DATE

  TARGET CONTACTS

1/1/2023

1

4/1/2023

2

7/1/2023

3

 

Here’s my problem:

Since we don’t have end-of-quarter contact totals, if I’m within the same quarter, I just need to see how many contacts we have vs. target (this is calculating correctly). If, however, the quarter is completed, I need to compare the contact total for the FOLLOWING quarter start date with the previous quarter target. Any idea how I can go about doing that?

 

hrafnkel11_0-1681396466097.png

 

1 ACCEPTED SOLUTION
hrafnkel11
Helper I
Helper I

I seem to have resolved this. I'm sure there's a more elegant way to handle this, but for posterity's sake, this is what I did. It's a calculated column that basically shifts contact counts back one period with some exception handling for current quarter numbers.

 

Contact Total_Next Date = 
var maxperiod=calculate(max('CONTACT'[AS_OF_DATE]),ALLEXCEPT('CONTACT','CONTACT'[ID])) // max as_of_date with no filters except ID
var actualperiod='CONTACT'[AS_OF_DATE] //as_of_date of the row
var nextperiod=calculate(MIN('CONTACT'[AS_OF_DATE]),ALLEXCEPT('CONTACT','CONTACT'[ID]),'CONTACT'[AS_OF_DATE]>actualperiod) //min as_of_date after as_of_date of row
var actualscontact=calculate(MAX('CONTACT'[CONTACT_COUNT]),ALLEXCEPT('CONTACT','CONTACT'[ID]),'CONTACT'[AS_OF_DATE]=actualperiod) //max contact count for as_of_date of row
var nextcontact=calculate(MIN('CONTACT'[CONTACT_COUNT]),ALLEXCEPT('CONTACT','CONTACT'[ID]),'CONTACT'[AS_OF_DATE]=nextperiod) //min contact count of next as_of_date
return
if(actualperiod=maxperiod,'CONTACT'[CONTACT_COUNT], //if as_of_date = max period then contact count
if(actualperiod = CALCULATE(max(DATE_T[DATE]),DATE_T[CURRENT_MONTH_INT]=0),'CONTACT'[CONTACT_COUNT], //if beginning of current quarter then use beginning of quarter count
    nextcontact //if min period = as_of_date of row, then just do contact_count, else do max count for row - max count for prior row))
    )) 

 

 

View solution in original post

1 REPLY 1
hrafnkel11
Helper I
Helper I

I seem to have resolved this. I'm sure there's a more elegant way to handle this, but for posterity's sake, this is what I did. It's a calculated column that basically shifts contact counts back one period with some exception handling for current quarter numbers.

 

Contact Total_Next Date = 
var maxperiod=calculate(max('CONTACT'[AS_OF_DATE]),ALLEXCEPT('CONTACT','CONTACT'[ID])) // max as_of_date with no filters except ID
var actualperiod='CONTACT'[AS_OF_DATE] //as_of_date of the row
var nextperiod=calculate(MIN('CONTACT'[AS_OF_DATE]),ALLEXCEPT('CONTACT','CONTACT'[ID]),'CONTACT'[AS_OF_DATE]>actualperiod) //min as_of_date after as_of_date of row
var actualscontact=calculate(MAX('CONTACT'[CONTACT_COUNT]),ALLEXCEPT('CONTACT','CONTACT'[ID]),'CONTACT'[AS_OF_DATE]=actualperiod) //max contact count for as_of_date of row
var nextcontact=calculate(MIN('CONTACT'[CONTACT_COUNT]),ALLEXCEPT('CONTACT','CONTACT'[ID]),'CONTACT'[AS_OF_DATE]=nextperiod) //min contact count of next as_of_date
return
if(actualperiod=maxperiod,'CONTACT'[CONTACT_COUNT], //if as_of_date = max period then contact count
if(actualperiod = CALCULATE(max(DATE_T[DATE]),DATE_T[CURRENT_MONTH_INT]=0),'CONTACT'[CONTACT_COUNT], //if beginning of current quarter then use beginning of quarter count
    nextcontact //if min period = as_of_date of row, then just do contact_count, else do max count for row - max count for prior row))
    )) 

 

 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.