Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
roley
Frequent Visitor

Dynamically Reporting on Consecutive Values across Time

 

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)

 

 

data.jpg

 

 

4 REPLIES 4
v-huizhn-msft
Microsoft Employee
Microsoft Employee

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

Hi Angelia,

 

Please find a link to the table data below:-

 

PBI Table

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

Hi Angelia,

 

Please find a link to the pbix attahced... 

 

PBIX FILE

 

Thanks for your help..

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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