Dynamically Reporting on Consecutive Values across Time

[roley]

Hi,

I’m new to PBI and need some help with a DAX formula.

I have used a column formula to basically display consecutive values (see below).  This is great but when I come to apply a filter it doesn’t change dynamically, I think I need to use a measure formula but am unsure of how to apply this.

Basically, I want the report to recalculate the consecutive values when I change the date filter from Min date to Max date.

Column formula: -

Is Consecutive =

           CALCULATE(

           COUNTROWS(AllocationSums),

           FILTER(ALL(AllocationSums),

               AllocationSums[Factory]=EARLIER(AllocationSums[Factory])

               &&AllocationSums[WeekN]=EARLIER(AllocationSums[WeekN])+0 && AllocationSums[CI UTIL%] >= 90

               )

)

Consecutive Times =

SWITCH (

   TRUE (),

   AllocationSums[Validation] = 0, 0,

   CALCULATE (

       COUNTROWS ( AllocationSums ),

       FILTER (

           ALL ( AllocationSums ),

           AllocationSums[Validation] = 0

               && AllocationSums[Factory] = EARLIER ( AllocationSums[Factory] )

               && AllocationSums[WeekN] <EARLIER ( AllocationSums[WeekN] )

       )

   )

       = 0, CALCULATE (

       SUM ( AllocationSums[Validation] ),

       FILTER (

           ALL ( AllocationSums ),

           AllocationSums[Factory] = EARLIER ( AllocationSums[Factory] )

               && AllocationSums[Week] <= EARLIER ( AllocationSums[Week] )

       )

   ),

   CALCULATE (

       SUM ( AllocationSums[Validation] ),

       FILTER (

           ALL ( AllocationSums ),

           AllocationSums[Factory] = EARLIER ( AllocationSums[Factory] )

               && AllocationSums[Week]

                   > CALCULATE (

                       MAX ( AllocationSums[Week] ),

                       FILTER (

                            ALL ( AllocationSums ),

                           AllocationSums[Validation] = 0

                               && AllocationSums[Factory] = EARLIEST ( AllocationSums[Factory] )

                               && AllocationSums[Week] < EARLIEST ( AllocationSums[Week] )

                       )

                   )

               && AllocationSums[Week] <= EARLIER ( AllocationSums[Week] )

       )

   )

)

Validation = IF(AllocationSums[Is Consecutive]>=1,1,0)

[v-huizhn-msft]

Hi @roley,

Which field is used to filter? From your formula, the [Is Consecutive] is not related to [Consecutive Times]? Could you please share your sample table in table rather than screenshot, so we can reproduce it rather than type the date manully?

Best Regards,
Angelia

[roley]

Hi Angelia,

Please find a link to the table data below:-

PBI Table

[v-huizhn-msft]

Hi @roley,

Which field is used to filter? You'd better share your .pbix file and list the desired result, so that we can post the solution which is close to your requirement.

Best regards,
Angelia

[roley]

Hi Angelia,

Please find a link to the pbix attahced... 

PBIX FILE

Thanks for your help..