如何计算DAX中的每日人口 [英] How to calculate daily population in DAX
问题描述
我正在尝试使用DAX和Power Pivot,使用患者的预约日期(开始日期)及其释放日期来计算给定日期在我们中心的每日患者人数。我计划最终在PowerBI中使用它。
I'm trying to calculate the daily population of patients at our center for any given date using the patient's booking date (start date) and their release date -- using DAX and Power Pivot. I'm planning on using it in PowerBI eventually.
我尝试了以下公式。我没有收到任何错误,但是当我在excel中创建数据透视表/图表时,却没有得到正确的输出。
I've tried the formula below. I'm not getting any errors, but when I go to create a pivot table/chart in excel, I'm not getting the correct output.
我只使用两个表:1)一个包含患者数据的主表,以及2)一个日期表(日历),并且已经在两个表之间建立了关系。
I'm only using two tables: 1) a main table with the patient data and 2) a date table (calendar) and have established a relationship between the two tables.
预订表具有患者ID,预订日期和发布日期列。我希望能够创建一个图表,其中将两个日期都考虑在内,显示每天的总人口。
The booking table has Patient ID, Booking Date, Release Date columns. I'd like to be able to create a graph showing the total population by day taking both dates into consideration.
DailyPop :=
CALCULATE (
COUNTROWS ( Patients ),
FILTER (
Patients,
AND (
Patients[Booking Date] > MIN ( 'Calendar'[Date] ),
Patients[Release Date] < MAX ( 'Calendar'[Date] )
)
)
)
推荐答案
您可以在日期表上添加额外的列:
You can add an extra column on your date table:
PatientCount:= SUMX(Patients,IF(Patients[Booking Date]<='Calendar'[Date] && Patients[Release Date]>='Calendar'[Date],1,0))
这篇关于如何计算DAX中的每日人口的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!