Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

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

PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!


Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors