Forum Discussion

Clout's avatar
Clout
Helper III
5 years ago
Solved

Counting rows between a specific timerange for every year

Hello guys,

 

I want to make a IF-Statement to count all values which exceeded a certain limit. But the limit only is there between the dates 1.5 and 1.11 (D.MM). I think this would be possible with declaring a specific start and end date. But my dataset goes from 2015 till 2021 and it is increasing. And I want to count the exceeded values for the another years too.

 

My Measure is now like this:

 

n_TN =
CALCULATE(COUNTROWS(Frachten),FILTER(Frachten, Frachten[AK_C_TN] <> Blank() && Frachten[AK_C_TN] > Max('Global'[GW_AblaufNges]))) + 0

 

But is there a way to only count the values between the timerange from 1.5 and 1.11 for every year?

 

Thanks in advance!

Need Help
  • BA_Pete's avatar
    BA_Pete
    5 years ago

    Hi Clout ,

     

    You would add something like this into your measure:

    n_TN =
CALCULATE(
  COUNTROWS(Frachten),
  FILTER(
    Frachten,
    Frachten[AK_C_TN] <> Blank()
    && Frachten[AK_C_TN] > Max('Global'[GW_AblaufNges])
    && MONTH(Frachten[Date]) IN {5, 6, 7, 8, 9, 10}
  )
) + 0

     

    I'm assuming that your 01/11 (DD/MM) end date is actually only up to 31/10.

     

    Pete

     

  • CNENFRNL's avatar
    CNENFRNL
    5 years ago
    =
COUNTROWS(
    FILTER(
        Frachten,
        Frachten[AK_C_TN] <> BLANK()
            && Frachten[AK_C_TN] > MAX( 'Global'[GW_AblaufNges] )
            && MONTH( Frachten[Date] ) IN { 5, 6, 7, 8, 9, 10 }
    )
) + 0
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Clout ,

     

    I have built a simply data sample as shown below:

     

    Based on my test, you could try the following formula to create a flag measure first:

    Flag =
IF (
    MAX ( 'Frachten'[AK_C_TN] ) <> BLANK ()
        && MAX ( 'Frachten'[AK_C_TN] ) > MAX ( 'Frachten'[GW_AblaufNges] )
        && MONTH ( MAX ( 'Frachten'[Date] ) ) IN { 5, 6, 7, 8, 9, 10 },
    1,
    0
)

    Then use SUMX() to sum Flag measure based on each year:

    Measure = SUMX(FILTER('Frachten','Frachten'[Year]=MAX('Frachten'[Year])),[Flag])

    Here is the final output :

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

4 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    5 years ago

    Hi Clout ,

     

    You would add something like this into your measure:

    n_TN =
CALCULATE(
  COUNTROWS(Frachten),
  FILTER(
    Frachten,
    Frachten[AK_C_TN] <> Blank()
    && Frachten[AK_C_TN] > Max('Global'[GW_AblaufNges])
    && MONTH(Frachten[Date]) IN {5, 6, 7, 8, 9, 10}
  )
) + 0

     

    I'm assuming that your 01/11 (DD/MM) end date is actually only up to 31/10.

     

    Pete

     

    • CNENFRNL's avatar
      CNENFRNL
      Community Champion
      5 years ago
      =
COUNTROWS(
    FILTER(
        Frachten,
        Frachten[AK_C_TN] <> BLANK()
            && Frachten[AK_C_TN] > MAX( 'Global'[GW_AblaufNges] )
            && MONTH( Frachten[Date] ) IN { 5, 6, 7, 8, 9, 10 }
    )
) + 0
    • BA_Pete's avatar
      BA_Pete
      Super User
      5 years ago

      Hi Clout ,

       

      Can you let me know whether this answer worked for you or not please?

      If it worked ok, please mark as the solution to help people with similar problems find the answer quicker.

       

      Thanks,

       

      Pete

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Clout ,

     

    I have built a simply data sample as shown below:

     

    Based on my test, you could try the following formula to create a flag measure first:

    Flag =
IF (
    MAX ( 'Frachten'[AK_C_TN] ) <> BLANK ()
        && MAX ( 'Frachten'[AK_C_TN] ) > MAX ( 'Frachten'[GW_AblaufNges] )
        && MONTH ( MAX ( 'Frachten'[Date] ) ) IN { 5, 6, 7, 8, 9, 10 },
    1,
    0
)

    Then use SUMX() to sum Flag measure based on each year:

    Measure = SUMX(FILTER('Frachten','Frachten'[Year]=MAX('Frachten'[Year])),[Flag])

    Here is the final output :

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