Quarter Wise Total number of Partner

[Shaji]

Hi There

I have a table with PartnerID, Type, URL and Date Column.  Basically there are 3 types of Partners and I need the count of Total Partners Quarter Wise.

Table Name: Partners

Now I calculate the number of Partners using the below measures:

TotalPlatinumPartners = CALCULATE(DISTINCTCOUNT(Partners[Partner_id]), Partners[Type]="Platinum")
TotalGoldPartners = CALCULATE(DISTINCTCOUNT(Partners[Partner_id]), Partners[Type]="Gold")
TotalSilverPartners =
CALCULATE(
DISTINCTCOUNT(Partners[Partner_id]),
FILTER('Partners',[Type]="Silver"),
FILTER('Partners', Partners[Url] <> BLANK()),
FILTER('Partners', SEARCH( "127.0.0.1", 'Partners'[Url], 1, 0 ) = 0 ))

Now I get the TotalPartnerCount like this:

TotalPartners = [TotalPlatinumPartners]+[TotalGoldPartners]+[TotalSilverPartners]

My requirement now is to get the TotalPartners QuarterWise. i.e

FY20Q4=? FY20Q3=? FY20Q2=?  and FY20Q1=? 

Note: I get the Sales of Particular Partner Quarter wise using the below measure:

CurrentQtrPlatinumSales = CALCULATE(SUM(Partners[PlatinumSales]),FILTER(ALLSELECTED(Partners),Partners[NumericNewQuarterYear] = MAX(Partners[NumericNewQuarterYear])))

where NumericNewQuarterYear is the Date column. I tried to use the similar method to get the TotalPartner however i was not successful. Can anyone help me out in this situation?

[v-xicai · Accepted solution]

Hi @Shaji ,

You may create a measure like DAX below.

CurrentQtrSales =
CALCULATE (
    SUM ( 'Partners'[PlatinumSales] ),
    FILTER (
        ALLEXCEPT ( 'Partners', 'Partners'[Type] ),
        YEAR ( 'Partners'[NumericNewQuarterYear] )
            = YEAR ( MAX ( 'Partners'[NumericNewQuarterYear] ) )
            && QUARTER ( 'Partners'[NumericNewQuarterYear] )
                = QUARTER ( MAX ( 'Partners'[NumericNewQuarterYear] ) )
            && 'Partners'[Url] <> BLANK ()
            && SEARCH ( "127.0.0.1", 'Partners'[Url], 1, 0 ) = 0
    )
)

Best Regards,

Amy 

Community Support Team _ Amy

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

[Shaji]

Anybody who can help here??? Waiting for your help

[amitchandak]

@Shaji , Not very clear.Can you explain

In case you need this qtr vs last qtr

With Date or Qtr Table

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))


//Can work with Qtr and Date table
Column in Date/Qtr 
Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)	

This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

[Shaji]

Hi Amit, 

May be I should I have explained it more clearly.

My requirement is to get the TotalPartners QuarterWise. i.e

FY20Q4=? FY20Q3=? FY20Q2=?  and FY20Q1=? 

I was able to get the Sales Amount Quarter Wise which I have mentioned above with the Measure. Now I want the number of Partners Quarter Wise. 

Total Number of Partners in

FY20Q4 = ?

FY20Q3 = ? 

FY20Q2 =? 

and FY20Q1=?