Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
spandy34
Responsive Resident
Responsive Resident

Number of Records End of Month based on alternative Month Field

‎11-21-2024 04:49 PM

Hi

I have a table called Agency Workers The data has a Month Field Column B. I then want to create a matrix which calculates

the DISTINCT number of LinkID Column E for each month where the End date in Role Column D is between the Month Field For example:-

 

For Month May 2024 calculate the distinct LinkID where End date in Role Between 01/05/2024 – 31/05/2024 = 33 Records

For Month June 2024 calculate the distinct LinkID where End Date in Role Between 01/06/2024 – 30/06/2024 = 24 Records

For Month July 2024 calculate the distinct LinkID where End date in Role Between 01/07/2024 – 31/07/2024 = 33 Records

 

I tried the following DAX but the values are not working.

 


End Date Numbers = CALCULATE( DISTINCTCOUNT('Agency Workers'[LinkID]),

FILTER( 'Agency Workers', 'Agency Workers'[End date in role] >= DATE(YEAR('Agency Workers'[Month]), MONTH('Agency Workers'[Month]), 1) && 'Agency Workers'[End date in role] <= EOMONTH('Agency Workers'[Month], 0) ) )

 

I would like the format as the following so I will put the Month field in the as a Row and the End Date Numbers as a Value

Can someone please write the DAX measure for this result

spandy34_0-1732234242354.png

Financial YearMonthStart Date in RoleEnd date in roleLinkIDIndex
2024-25Jul-2427/02/202423/07/2024ADEJ Recruitment Ltd4612
2024-25Jul-2402/04/202409/07/2024ANDM Temp Recruitment Ltd4570
2024-25May-2429/04/202417/05/2024ANGD Sanctuary Personnel Limited3929
2024-25Jul-2404/01/202423/07/2024ANGDTemp Recruitment Ltd4605
2024-25May-2401/11/202317/05/2024ANGH Temp Recruitment Ltd4099
2024-25Jul-2418/03/202423/07/2024ANNMTemp Recruitment Ltd4614
2024-25Jun-2404/06/202413/06/2024ANTT Temp Recruitment Ltd4275
2024-25May-2408/09/202324/05/2024ARSH Temp Recruitment Ltd4114
2024-25Jun-2425/06/202428/06/2024AZAP Temp Recruitment Ltd4268
2024-25Jun-2415/04/202410/06/2024BRAA  Temp Recruitment Ltd4291
2024-25Jul-2403/05/202405/07/2024BRAI Temp Recruitment Ltd4573
2024-25May-2415/04/202431/05/2024CARJ Temp Recruitment Ltd4084
2024-25May-2401/09/202230/05/2024CARS Sellick Partnership Limited3873
2024-25Jun-2407/08/202303/07/2024CHEW Caritas Recruitment Limited4135
2024-25Jul-2403/01/202423/07/2024CHRP Temp Recruitment Ltd4597
2024-25May-2421/05/202424/05/2024CHRS Temp Recruitment Ltd4029
2024-25Jul-2408/01/202423/07/2024CHRO Temp Recruitment Ltd4608
2024-25May-2420/10/202310/05/2024COLA  Temp Recruitment Ltd4087
2024-25Jul-2404/01/202423/07/2024DAVB Temp Recruitment Ltd4606
2024-25May-2402/04/202403/05/2024DAVA Temp Recruitment Ltd4059
2024-25Jul-2415/04/202423/07/2024DAVB Temp Recruitment Ltd4598
2024-25Jun-2421/05/202406/06/2024DAVD Temp Recruitment Ltd4252
2024-25May-2426/02/202420/05/2024DAWD  Eden Brown Limited3911
2024-25Jul-2418/09/202312/07/2024DEBD Temp Recruitment Ltd4603
2024-25Jun-2405/04/202407/06/2024ETHB Temp Recruitment Ltd4246
2024-25Jun-2406/02/202428/06/2024EVIG Derbyshire Caritas Recruitment Limited4152
2024-25May-2420/09/202310/05/2024FARA Temp Recruitment Ltd4097
2024-25May-2425/05/202425/05/2024FREY Temp Recruitment Ltd4070
2024-25May-2408/09/202324/05/2024GARL  Temp Recruitment Ltd4081
2024-25Jul-2424/06/202423/07/2024GILM Temp Recruitment Ltd4619
2024-25May-2418/09/202324/05/2024GURS Temp Recruitment Ltd4089
2024-25May-2410/07/202303/05/2024IANL Temp Recruitment Ltd4078
2024-25Jul-2414/11/202205/07/2024IANM Temp Recruitment Ltd4554
2024-25May-2409/04/202403/05/2024JACG Temp Recruitment Ltd4019
2024-25Jul-2422/04/202426/07/2024JACH Eden Brown Limited4396
2024-25May-2405/02/202424/05/2024JACD Temp Recruitment Ltd4122
2024-25Jul-2424/03/202105/07/2024JAMC Temp Recruitment Ltd4553
2024-25May-2402/04/202431/05/2024JAMD Temp Recruitment Ltd4052
2024-25May-2412/09/202229/05/2024JANW  Eden Brown Limited3874
2024-25Jun-2415/04/202401/06/2024JERA STANDBY HEALTHCARE (NORTH) LTD4178
2024-25Jun-2401/04/202427/06/2024JOAC Randstad Solutions Limited4171
2024-25May-2404/03/202410/05/2024JOHM Temp Recruitment Ltd4005
2024-25May-2406/02/202403/05/2024JOSW Temp Recruitment Ltd4033
2024-25May-2404/09/202303/05/2024JULD Temp Recruitment Ltd4094
2024-25May-2422/11/202317/05/2024KATW Temp Recruitment Ltd4100
2024-25Jun-2421/12/202307/06/2024KEIA Temp Recruitment Ltd4244
2024-25May-2415/09/202303/05/2024KEIH Temp Recruitment Ltd4096
2024-25Jul-2405/09/201619/07/2024KENL Temp Recruitment Ltd4537
2024-25May-2408/09/202310/05/2024KEVG  Temp Recruitment Ltd4079
2024-25Jun-2415/04/202410/06/2024KEVT Temp Recruitment Ltd4303
2024-25Jun-2405/04/202407/06/2024LEOW Temp Recruitment Ltd4277
2024-25Jul-2416/04/202407/07/2024LEWR Temp Recruitment Ltd4545
2024-25Jun-2404/09/202314/06/2024LISSTemp Recruitment Ltd4328
2024-25Jul-2409/04/202423/07/2024LUCK Temp Recruitment Ltd4617
2024-25May-2405/04/202403/05/2024LUKM Temp Recruitment Ltd4022
2024-25May-2404/12/202331/05/2024LYNM Sanctuary Personnel Limited3891
2024-25Jun-2404/12/202304/06/2024LYNM Sanctuary Personnel Limited4142
2024-25Jul-2430/03/202305/07/2024MART Temp Recruitment Ltd4557
2024-25Jul-2405/04/202405/07/2024MARW Temp Recruitment Ltd4552
2024-25Jul-2409/10/202301/07/2024MATB Temp Recruitment Ltd4601
2024-25Jul-2415/01/202423/07/2024MERD Temp Recruitment Ltd4607
2024-25May-2416/04/202401/05/2024MERM emp Recruitment Ltd4024
2024-25Jul-2404/09/202301/07/2024MIRE  Temp Recruitment Ltd4602
2024-25Jul-2415/01/202423/07/2024MOHK Temp Recruitment Ltd4610
2024-25May-2408/09/202310/05/2024MONM l Temp Recruitment Ltd4092
2024-25Jul-2415/04/202423/07/2024NASR Temp Recruitment Ltd4616
2024-25Jun-2420/11/202314/06/2024NATC  Temp Recruitment Ltd4331
2024-25May-2408/01/202410/05/2024NICE Temp Recruitment Ltd4083
2024-25Jun-2414/02/202427/06/2024NIGC Corepeople Recruitment Limited4155
2024-25Jun-2427/02/202414/06/2024NIKJ  Sanctuary Personnel Limited4157
2024-25Jun-2427/05/202428/06/2024PAMSRandstad Solutions Limited4179
2024-25Jul-2405/02/202405/07/2024PHIM Temp Recruitment Ltd4561
2024-25Jun-2415/04/202421/06/2024REBW Temp Recruitment Ltd4346
2024-25May-2406/11/202324/05/2024REGA Temp Recruitment Ltd4104
2024-25Jul-2408/01/202423/07/2024RESE Temp Recruitment Ltd4609
2024-25Jul-2404/06/202426/07/2024RILH Temp Recruitment Ltd4565
2024-25Jul-2405/04/202430/07/2024ROBB Temp Recruitment Ltd4524
2024-25May-2420/11/202319/05/2024ROBT Service Care Solutions Limited3890
2024-25May-2414/05/202404/06/2024SARD Ross Staffing Solutions Limited3950
2024-25Jun-2414/05/202405/06/2024SARD Ross Staffing Solutions Limited4187
2024-25Jul-2404/01/202423/07/2024SARJ Temp Recruitment Ltd4604
2024-25Jun-2406/11/202328/06/2024SARS  Temp Recruitment Ltd4332
2024-25Jun-2404/09/202327/06/2024SHAB Temp Recruitment Ltd4234
2024-25May-2404/09/202310/05/2024STAC Temp Recruitment Ltd4090
2024-25Jun-2415/04/202428/06/2024STEH Temp Recruitment Ltd4320
2024-25Jun-2408/09/202328/06/2024STEH Temp Recruitment Ltd4321
2024-25Jul-2404/04/202205/07/2024STEW Temp Recruitment Ltd4559
2024-25Jun-2404/12/202314/06/2024TAMS Temp Recruitment Ltd4333
2024-25May-2428/11/202324/05/2024THEA Temp Recruitment Ltd4102
2024-25Jul-2415/04/202423/07/2024TINC Temp Recruitment Ltd4615
2024-25Jun-2422/09/202307/06/2024TRAB Temp Recruitment Ltd4329
2024-25Jul-2403/06/202423/07/2024VICB Temp Recruitment Ltd4618

 

 

 

 

 

 

@Anonymous @danextian @lbendlin @Greg_Deckler 

 

Labels:
Message 1 of 6
511 Views
0
1 ACCEPTED SOLUTION
FreemanZ
Super User
Super User
In response to spandy34

‎11-21-2024 10:15 PM

Hi @spandy34 ，

 

it is good to know [month] is actually date type, try like:

measure = 
CALCULATE(
    DISTINCTCOUNT(data[LinkID]),
    FILTER(
        data,
        EOMONTH(data[end date in role], 0) = EOMONTH(data[month], 0)
    )
)

 

it worked like:

FreemanZ_0-1732256080970.png

 

 

View solution in original post

spandy34.pbix
Message 4 of 6
433 Views
0
5 REPLIES 5
FreemanZ
Super User
Super User

‎11-21-2024 06:04 PM

Hi @spandy34 ，

 

try to plot a visual with Month column and a measure like:

 

measure =
CALCULATE(
    DISTINCTCOUNT(data[LinkID]),
    FORMAT(data[end date in role], "mmm-yy") = MAX(data[month])
)

 

 

it worked like:

FreemanZ_0-1732241046187.png

 

spandy34.pbix
Message 2 of 6
475 Views
0
spandy34
Responsive Resident
Responsive Resident
In response to FreemanZ

‎11-21-2024 07:34 PM

When setting the Month format to Date I get Error in Column field but if I put the Month to Text I get all the values in Month Column to False even though there should be True entries and my Table Visualisation looks like this 

spandy34_0-1732249171798.png

 

 

spandy34_0-1732247842342.png

 

 

Message 3 of 6
461 Views
0
FreemanZ
Super User
Super User
In response to spandy34

‎11-21-2024 10:15 PM

Hi @spandy34 ，

 

it is good to know [month] is actually date type, try like:

measure = 
CALCULATE(
    DISTINCTCOUNT(data[LinkID]),
    FILTER(
        data,
        EOMONTH(data[end date in role], 0) = EOMONTH(data[month], 0)
    )
)

 

it worked like:

FreemanZ_0-1732256080970.png

 

 

spandy34.pbix
Message 4 of 6
434 Views
0
spandy34
Responsive Resident
Responsive Resident
In response to FreemanZ

‎11-22-2024 02:36 AM

The figures aren't quite adding up so I'll do a bit of digging around and see what is going on but it's good I've got a base to work with . I'll keep you informed how I get on . I'm in hospital so will look at it on Monday and let you know .

Message 5 of 6
410 Views
0
spandy34
Responsive Resident
Responsive Resident
In response to FreemanZ

‎11-26-2024 08:58 AM

Thank you so much . That has worked . I appreciate your help 

Message 6 of 6
366 Views
0

Helpful resources

Announcements
November Fabric Update Carousel

Fabric Monthly Update - November 2025

Check out the November 2025 Fabric update to learn about new features.

Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All
Users online (25)