Hi,
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 ID | Licenses | Unit | Date | Status | Class |
| 123456 | 1 | 12345 | 3/1/21 | Active | 4 |
| 987654 | 1 | 12345 | 3/1/21 | Active | 4 |
| 23456 | 2 | 12345 | 3/1/21 | Active | 4 |
It's connected to a date table, which other data sources are running off of
| Date | Week Ending | First of Month |
| 2/27/21 | 3/5/21 | 2/1/21 |
| 2/28/21 | 3/5/21 | 2/1/21 |
| 3/1/21 | 3/5/21 | 3/1/21 |
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.
