Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone! Could you please advise me on below scenario
I get 2 source files (Excel) named HeadCount and Terminations every week and I combine latest 6 of each of these files and load them to PowerBI. Both the files have a load date column which differentiates the data from week to week.
I use load date column from HeadCount file to count the number of employees each week and termination date column from Terminations file to count nuumber of terminations in a week.
Now I have a requirement to calculate the count of employees that exists in prior week AND doesn't exists in current week AND doesn't exist in Terminations file.
Here is the link to my sample datasets:
https://1drv.ms/f/s!AtqcKHVDSd3QghmFZSDgcfcVhoOJ
My snapshot of data below
Consider 2/2/2019 is current week and 1/26/2019 is prior week. My condition is emp id (EXISTS IN PRIOR WEEK AND DOESNT EXIST IN CURRENT WEEK) to be checked from HeadCount file AND (DOESNT EXIST IN TERMINATIONS) to be checked from Terminations file. Now, If you look at my HeadCount data, Emp ID s 4,5,6 exists in prior week(1/26) and doesnt exists in current week (2/2). Then comparing it with Terminations file for current week (2/2), Emp IDs 5 and 6 doesnt exist in Terminations.
So my result should be 2 which is count of employee ids that satisfies the above three conditions.
Any ideas how this can be implemented in DAX? Or any other ideas how this can be achieved?
Hoping for a solution here in this forum
Greatly appreciate your response and efforts!
Hi,
Share sample datasets and show the expected result.
@Ashish_Mathur Hi, I edited my original post to include link for sample datasets and snapshots of my data for a quick view.
Thank you!
Hi,
Please download my PBI file from here.
Hope this helps.
@Ashish_MathurThank you so very much for your efforts. Unfortunately, I am not supposed to use any filters on the week. Is there a way we can get the count without using slicer on the week number?
Hi,
You are welcome. If you cannot use a filter/slicer for selecting the week, then how will you define the current or previous week?
Hi @Ashish_Mathur,
Current week and Prior week will be based on the Load Date. Like
Current week: WEEKNUM(Headcount[LoadDate],2)=WEEKNUM(TODAY(),2)
Prior week: WEEKNUM(Headcount[LoadDate],2)=WEEKNUM(TODAY(),2)-1
We will have to find a solution that incorporates the date filters in the DAX logic.
This is how I combine 6 weeks headcount data to a single file and extract load date from file name and pull into power BI, if this helps. The same is for Terminations file too.
let
Source = SharePoint.Files("Sharepoint Path", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "Headcount FTE as of")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date created", Order.Descending}}),
#"Kept First Rows" = Table.FirstN(#"Sorted Rows",6),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "Name", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Name.1", "Name.2", "Name.3", "Name.4", "Name.5", "Name.6", "Name.7"}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Removed Columns", {{"Name.7", each Text.BeforeDelimiter(_, "."), type text}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Renamed Columns", {{"Date", type text}}, "en-US"),{"Month", "Date"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Merged", "Year"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged.1"),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Excel.Workbook([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Merged.1", "LoadDate"}}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Renamed Columns1", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each Text.Contains([Custom.Name], "Headcount FTE Details")),
#"Expanded FormatHeader" = Table.ExpandTableColumn(#"Removed Columns4", "FormatHeader", {"Column Header1, Column Header2, ........................................"})
in
#"Expanded FormatHeader"
Hi,
I'll take quite some time to solve this. It's better if someone else helps you with this.
@Ashish_Mathur No problem. Thanks for your time. Let me know if you find a solution anytime. 'Coz I don't even think Im close to it 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |