Counting rows between a specific timerange for every year

[Clout]

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!

[BA_Pete · Accepted solution]

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 · Accepted solution]

=
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

[v-eqin-msft · Accepted solution]

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.

[BA_Pete · Accepted solution]

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

[BA_Pete]

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

[CNENFRNL · Accepted solution]

=
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