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

[DistinctlyDAX]

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

    )

)

[WolfBiber]

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

Thx and greetings. 

[DistinctlyDAX]

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]

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

Fred

[WolfBiber]

@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]

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]

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.