Count rows from another table

[Anonymous]

Hi,

I have two tables: AllGroups - with the list of Group titles (62 rows) and SummaryAboutGroups - with execution dates of those groups (1799 rows):

I need to get this answer: how many groups were executed in special period of time - this week/last week/today and so on.

So the result should be one table of 62 rows with two columns:

Group|

Number of groups

If in that period of time some Groups were not executed, there should be zeros in Number of Groups column.

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

We can use the following steps to meet your requirement.

1. In Power Query Editor, we need to add a date column and a week column.

2. Create three measures.

Today number of Group = 
var _today = TODAY()
return
CALCULATE(COUNT('Table'[Group]),FILTER('Table','Table'[Date]=_today))

This week number of Group = 
var _thisweek = WEEKNUM(TODAY(),2)
return
CALCULATE(COUNT('Table'[Group]),FILTER('Table','Table'[Week of Year]=_thisweek))

Last week number of Group = 
var _lastweek = WEEKNUM(TODAY(),2)-1
return
CALCULATE(COUNT('Table'[Group]),FILTER('Table','Table'[Week of Year]=_lastweek))

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[swise001]

@Anonymous 

Here's one way: 

Create a simple date table that spans the range of the dates in your model: 
(There are better ways to do this - but for the sake of this example: )

Here's my sample group updated table: 

Then just include a measure to count rows based on the dates selected in a slicer: 

Groups Updated =
CALCULATE (
COUNTROWS ( GroupUpdates ),
DATESBETWEEN (
GroupUpdates[GroupStartTime],
MIN ( DateTable[Date] ),
MAX ( DateTable[Date] )
)
)

Example of the result: 

[amitchandak]

@Anonymous , Join to both tables on Group.

Create a date column in Summaryaboutgroup

group start date = [groupstarttime].date

Join this date with the date calendar. Have a week, month, qtr in your calendar. 

Analyze with help from date and group tables

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — QTD
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
Power BI — Day Intelligence
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

[Mariusz]

Hi @Anonymous 

You can create a Measure for each time frame using DAX Time inteligence functions, please refer to the below.

https://docs.microsoft.com/en-us/dax/time-intelligence-functions-dax 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn