Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
harshad_barge
Helper I
Helper I

Group by anniversary (Date)

I have a dataset which has the join date of an individual. "Position From Date" and "Position To Date".

 My code is currently grouping by:

Employee Summary By Anniversary = GROUPBY(
'ORDINARY HOURS',
'ORDINARY HOURS'[Employee_ID],
'ORDINARY HOURS'[Employee Name],
'ORDINARY HOURS'[Dim_Employee_Position.Anniversary date],
'ORDINARY HOURS'[Level],
"From Date", MINX(
CURRENTGROUP(),
'ORDINARY HOURS'[Calculation Date]
),
"To Date", MAXX(
CURRENTGROUP(),
'ORDINARY HOURS'[Calculation Date]
),
"Actual Pay", SUMX(
CURRENTGROUP(),
'ORDINARY HOURS'[Payroll.Gross Pay]
),
"Calculated Pay", SUMX(
CURRENTGROUP(),
'Ordinary Hours'[CalculatedPay]
)
)

I want to Group by the anniversary of "Position From Date" every year 

 

Example: From 1/7/2012- 27/6/2013 "Calculation Date" column and 28/6/2013 to 27/6/2014 and so forth
How would I translate this logic to DAX?
Please find the example dataset here
Expected Table:

Employee NamePosition From DateFrom DateTo DateCalculated PayActual Pay
Randy28/06/20101/07/201027/06/2010$$$$
Randy28/06/201128/06/201127/06/2012$$$$
Randy28/06/201228/06/201227/06/2013$$$$
Randy28/06/201328/06/201327/06/2014$$$$
Randy28/06/201428/06/201427/06/2015$$$$



2 REPLIES 2
v-eachen-msft
Community Support
Community Support

Hi @harshad_barge ,

 

I downloaded the file and find that it is a zip file filled with xml files. I tried to import file but nothing success. Is it the right dataset?

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

@v-eachen-msft

 

I have found the file in question; sorry for the gap.

 

Please find the file here:

 

https://filebin.net/agi6ojcr4qduve8x/Example.zip?t=3geojdh4

 

Thanks,

Harshad

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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