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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I
Helper I

Date - Week Ending - Month Issue


I have two tables that I've semi fixed my issue, but still have an issue with cross over weeks. 


The first data set is licenses, for each month there is a file like the below which I consolidate into one table via folder.

Employee IDLicensesUnitDateStatusClass


It's connected to a date table, which other data sources are running off of

DateWeek EndingFirst of Month


Right now they are connected <> from Date on the 1st table to First of Month on the second.


When I show the formula below: calculate(SUM('09 License Information'[License]),'09 License Information'[Class]=4,'09 License Information'[Employee Status]="Active")


If I have date on the matrix, it shows me the value from the 2/1/21 file or the 3/1/21 file, I assume because I have the link as first of the month. The issue becomes when I take off the date, and switch to week ending, because that's really the level we want to analyze at, that crossover week doubles up. Example, it'll show the values from 2/1/21 and 3/1/21 on that, because of the double up.


How can I get it to only pull one of the counts when analyzing? I tried doing max dates, but cannot get the formula right to get it to pull. Ideally, it would just sum the head count of the latest file. Originally I attached Date to Date but then it only showed on the 1st of the month instead of each day. I attempted Date to Week Ending, but got 0 values. 


Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.