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: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
384 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
306 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
348 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
334 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
307 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
239 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
283 Views
0

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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

View All