Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi Helpful powerbi folks
I am trying to count the number of entities each month that have an action within the start date and end date of the action.
Entity 1
Action 1 Start date May 4 End date June 31
Action 2 Start date May 6 End date July 15
Action 3 Start Date July 2 End date July 27
Entity 1 should score 1 for May, June and July. When my report gets to August it should score zero.
Entity 2
Action Bob start date May 13 End Date June 2
Action Fred start date May 17 End Date May 18
Action Jim start date June 2 End Date August 27
Entity 2 should score 1 for May, June, July and August
The data i need then is
May 2
June 2
July 2
Aug 1
With that i can use a bar chart visualization to show how many practices have actions in May, June etc.
The number of actions is irrelevant, simply that an action is happening in the particular month.
How do I find the earliest Start Date, the latest End Date, and make a table for the 1st of each month starting with Start Date and ending with End date.
Using my simplistic data above earliest start date = May 4, so my "calendar table" Starts May 1 and my latest end date = Aug 27 so my "calendar table" ends Aug 1.
Once i have a list of all the months how do i evaluate if the month on the calender table is between or equal to the start date end date of the action?
Then when there is more than one action for an entity only count the entity as 1 (count distinct?)
I want to get to a visualization like this that i made in excel.
thanks to the smart folks that may be able to assist!
Solved! Go to Solution.
Hi,@iruserwin
As far as I know, there are only 30 days in June in 2024, and there is an error in the data you gave and what I learned, so I will use June 30 in the data below.
Regarding the issue you raised, my solution is as follows:
1.First I have created the following table and the column names and data are the data you have given:
2.Regarding your need to create a table that includes all start and end dates and ends with the 1st of the start month, I created the following calculation table:
dateTable =
VAR mindate=MINX(UNION(SELECTCOLUMNS('Entity 1',"1",'Entity 1'[Start date]),SELECTCOLUMNS('Entity 2',"1",'Entity 2'[start date])),[1])
VAR maxdate=MAXX(UNION(SELECTCOLUMNS('Entity 1',"2",'Entity 1'[End date]),SELECTCOLUMNS('Entity 2',"2",'Entity 2'[End Date])),[2])
RETURN
CALENDAR (DATE (2024, MONTH(mindate), 1), DATE (2024, MONTH(maxdate), 1))
3. Regarding your first demand statistic how many operations per month:
I created the following measure and calculation table:
Measure = COUNTROWS(FILTER(SUMMARIZE(ADDCOLUMNS(CALENDAR(MIN('Entity 1'[Start date]),MAX('Entity 1'[End date])),"Month",FORMAT([Date],"MMMM")),[Month]),[Month]=SELECTEDVALUE(result[Month])))+COUNTROWS(FILTER(SUMMARIZE(ADDCOLUMNS(CALENDAR(MIN('Entity 2'[Start date]),MAX('Entity 2'[End date])),"Month",FORMAT([Date],"MMMM")),[Month]),[Month]=SELECTEDVALUE(result[Month])))
result = SUMMARIZE('dateTable',dateTable[Date].[Month])
The results of the visualization are as follows:
4.Here's my final result, which I hope meets your requirements.
Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,@iruserwin
As far as I know, there are only 30 days in June in 2024, and there is an error in the data you gave and what I learned, so I will use June 30 in the data below.
Regarding the issue you raised, my solution is as follows:
1.First I have created the following table and the column names and data are the data you have given:
2.Regarding your need to create a table that includes all start and end dates and ends with the 1st of the start month, I created the following calculation table:
dateTable =
VAR mindate=MINX(UNION(SELECTCOLUMNS('Entity 1',"1",'Entity 1'[Start date]),SELECTCOLUMNS('Entity 2',"1",'Entity 2'[start date])),[1])
VAR maxdate=MAXX(UNION(SELECTCOLUMNS('Entity 1',"2",'Entity 1'[End date]),SELECTCOLUMNS('Entity 2',"2",'Entity 2'[End Date])),[2])
RETURN
CALENDAR (DATE (2024, MONTH(mindate), 1), DATE (2024, MONTH(maxdate), 1))
3. Regarding your first demand statistic how many operations per month:
I created the following measure and calculation table:
Measure = COUNTROWS(FILTER(SUMMARIZE(ADDCOLUMNS(CALENDAR(MIN('Entity 1'[Start date]),MAX('Entity 1'[End date])),"Month",FORMAT([Date],"MMMM")),[Month]),[Month]=SELECTEDVALUE(result[Month])))+COUNTROWS(FILTER(SUMMARIZE(ADDCOLUMNS(CALENDAR(MIN('Entity 2'[Start date]),MAX('Entity 2'[End date])),"Month",FORMAT([Date],"MMMM")),[Month]),[Month]=SELECTEDVALUE(result[Month])))
result = SUMMARIZE('dateTable',dateTable[Date].[Month])
The results of the visualization are as follows:
4.Here's my final result, which I hope meets your requirements.
Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
84 | |
66 | |
62 | |
61 |
User | Count |
---|---|
197 | |
118 | |
108 | |
78 | |
69 |