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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
kmes912
Helper I
Helper I

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.