topic COUNT if DATESINPERIOD with 2 columns in DAX Commands and Tips

COUNT if DATESINPERIOD with 2 columns

DataHero — Tue, 31 Oct 2023 22:51:54 GMT

Hi,

I need help with a DAX measure. I want to perform a COUNT. This COUNT should be filtered by DATESINPERIOD, which is 2022. The challenge is, that the date actually can be in 1 of 2 columns. Lets say Schritt10 or Schritt11 for example. How can i write the dax measure?

I tried some measures but they always end in errors : )

For example:

try no. 1: CountIn2022 = COUNTROWS( FILTER( YourTable, (YEAR(YourTable[Schritt10 ]) = 2022 || YEAR(YourTable[Schritt11]) = 2022)
try no. 2:

CountIn2022 =
VAR Schritt10 =

DATESINPERIOD (

YourTable[Schritt10Abgeschlossen],

DATE ( 2022, 01, 01 ),

YEAR

)

VAR Schritt11 =

DATESINPERIOD (

YourTable[Schritt11Abgeschlossen],

DATE ( 2022, 01, 01 ),

YEAR

)

RETURN
CALCULATE (

COUNT ( YourTable[Id] ),

(

Schritt10=TRUE() || Schritt11=TRUE()

))

I hope someone can help me.
Thank you in advance.

Re: COUNT if DATESINPERIOD with 2 columns

ppm1 — Tue, 31 Oct 2023 23:49:18 GMT

You could consider unpivoting your data to simplify things. This approach won't be performant at large scale, but here is one way to do it.

DIP = VAR dip = DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -1, YEAR ) VAR result = COUNTROWS ( FILTER ( YourTable, YourTable[Schritt10] IN dip || YourTable[Schritt11] IN dip ) ) RETURN result

If you have many duplicate rows in YourTable, this should be faster.

DIP2 = VAR dip = DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -1, YEAR ) VAR summarizedates = SUMMARIZE ( YourTable, YourTable[Schritt10], YourTable[Schritt11], "cCount", COUNT ( YourTable[Schritt10] ) ) VAR result = SUMX ( FILTER ( summarizedates, YourTable[Schritt10] IN dip || YourTable[Schritt11] IN dip ), [cCount] ) RETURN result

Pat

Re: COUNT if DATESINPERIOD with 2 columns

DataHero — Thu, 02 Nov 2023 10:38:14 GMT

Thank you ppm1,

the first approach works perfectly!
I didnt know about the IN.