cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DistinctlyDAX Frequent Visitor
Frequent Visitor

DISTINCTCOUNT for multiple periods with Custom Calendar 4-4-5

Cheers everyone. FYI, the below is still in an EXCEL 2016 workbook, I haven't ported it over to BI DESKTOP yet, I'm doing that soon.

 

QUESTION #1:

I've run into a situation which is stumping me. My FACT table contains Sales Reps who are categorized by the Queue they are in (total of 5 Queue types). At the start of any new fiscal month, a reps queue type can change. I want to create a Calculated Column called 'Prior Month Queue' that will capture the value of the queue type for that rep for the prior fiscal month and populate it accordingly. If the rep is a new hire, and therefore there is no value for the prior month, the result can be 'blank' or 'new hire'.

 

I have a separate PERIODS dim table with the dates information; keep in mind the months are FISCAL (4-4-5) type (and this happens to be a year with an 'extra week') so I’m using the GFITW method (https://powerpivotpro.com/2011/12/the-greatest-formula-in-the-world-part-one/) for Period numbering, etc. for my 'customer calendar'. Since it is a custom 4-4-5 calendar, I cannot use DAX built in time intellegence.

 

How would I go about doing this?

 

Here is the link to the Excel 2016 (64bit) workbook on OneDrive - https://1drv.ms/x/s!AnXK4iScnB9DifMhdk_DKtjb-2YvRQ

 

Many thanks!

 

 

QUESTION #2 -

Similar to question #1. I have a PivotTable in the Excel workbook that has the initial filter of 'FyrMonth' (i.e. 2006 Aug, 2006 Sep, etc) and I want to capture the DISTINCTCOUNT of the reps over a two month period for any given queue. Since the reps can change queues on any given month, I can't simply count them and have the results display via the PivotTable. So I need to do a DISTINCTCOUNT for the current month (easy) and then do a DISTINCTCOUNT for a two month period (hard) and subtract one from the other (easy).

 

As an example for one of the queue types "HV", I have the following formula to capture the CURRENT fiscal month (initial filter via the PivotTable), this works:

 

HV DISTINCTCOUNT v1

=CALCULATE (

    DISTINCTCOUNT ( NashDataTableALL[Badge] ),

    NashDataTableALL[ACDs] > 0,

    NashDataTableALL[Queue] = "HV"

)

 

Then I use the GFITW technique for the PRIOR FiscalMonth Period, this also works:

 

HV DISTINCTCOUNT v1 Prior Period

=

CALCULATE (

    [HV DISTINCTCOUNT v1],

    FILTER ( ALL ( Periods ), Periods[PeriodID] = MAX ( Periods[PeriodID] ) - 1 )

)

 

However, I cannot subtract one of these measures from the other, given that the 'HV' classification for any given rep can change at the beginning of any given period.

 

Thus, I need something like the below (which does NOT work, ERROR) to calculate this all at once for a two month fiscal year period:

 

HV DISTINCTCOUNT v2

=

CALCULATE (

    DISTINCTCOUNT ( NashDataTableALL[Badge] ),

    NashDataTableALL[ACDs] > 0,

    NashDataTableALL[Queue] = "HV",

    (

        FILTER ( ALL ( Periods ), Periods[PeriodID] = MAX ( Periods[PeriodID] ) - 1 )

            && FILTER ( ALL ( Periods ), Periods[PeriodID] = MAX ( Periods[PeriodID] ) )

    )

)

6 REPLIES 6
WolfBiber Member
Member

Re: DISTINCTCOUNT for multiple periods with Custom Calendar 4-4-5

Hey, please don't be offended, but for such a big and complex questionaire: hire a proffessional PowerBi Consultant.

 

Thx and greetings. 

DistinctlyDAX Frequent Visitor
Frequent Visitor

Re: DISTINCTCOUNT for multiple periods with Custom Calendar 4-4-5

WolfBiber,

I'm not offended at all by your comment, but somewhat surprised. I've seen much more complex solutions provided in this forum.

 

Cheers.

freder1ck Member
Member

Re: DISTINCTCOUNT for multiple periods with Custom Calendar 4-4-5

Haha. I hang out on these forums specifically for the complex problems! 

Fred

WolfBiber Member
Member

Re: DISTINCTCOUNT for multiple periods with Custom Calendar 4-4-5

@freder1ck perfect :), and sorry @DistinctlyDAX: I haven't seen that you provided an excel file with example data, thats makes it a lot easier. My previous post was related to many posts where people have a lot of questions without any example data, what makes it sometime very hard to understand the problems.

 

For your first question try following dax, maybe you have to modify it a bit:

Prior Month Queue = 
   CALCULATE(
     FIRSTNONBLANK(NashDataTableALL[Queue];1);
      FILTER(
         Periods;
         Periods[End]<=[Date])
      )

greetings

DistinctlyDAX Frequent Visitor
Frequent Visitor

Re: DISTINCTCOUNT for multiple periods with Custom Calendar 4-4-5

WolfBiber,

 

Many thanks for the response. I've tried using the measure your provided but am getting the following error:

 

The expression is not valid or appears to be incomplete. Please review and correct the expression.
The syntax for ';' is incorrect.

 

I've tried changing the ; but cannot seem to get the correct measure. Was that a typo on your end?

 

Cheers!

WolfBiber Member
Member

Re: DISTINCTCOUNT for multiple periods with Custom Calendar 4-4-5

Hey,

in my quick test it was working, but in your example Data there are too few to test it right, and you have to modify it a bit.

Your Data Model is also a confusing me, making it hard to understand what you exactly want to reach. So for perfect work of this formula, you have to develop it further, its just a starting point. 

I just changed your formula of your calculated column "Prior Month Queue" in NashDataTableAll.

 

 Greetings.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors