cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kmes912
Frequent Visitor

Date - Week Ending - Month Issue

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 IDLicensesUnitDateStatusClass
1234561123453/1/21Active4
9876541123453/1/21Active4
234562123453/1/21Active4

 

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

DateWeek EndingFirst of Month
2/27/213/5/212/1/21
2/28/213/5/212/1/21
3/1/213/5/213/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. 

0 REPLIES 0

Helpful resources

Announcements
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!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors