DAX expression to display data associated with past dates

[chanchanha]

 I created a table in Power BI that displayed the number of attendees for each months based the the end date of the training.  My table is grouped by years then months.  I only want to display attendee count from past training with past end date.  

I tried adding a measure:

Past Course=TOTALYTD(Sum(Training[Attendee_Count]), Training[End_Date])

When I dragged the measure field into my values, I get error. 

Is there something wrong with my DAX expression? Or is there a better way to get the table to only display attendee count of past courses?

[v-huizhn-msft]

Hi @chanchanha,

The Training[End_Date] is day level? You'd better create a calendar table and create relationship between calendar and real table. Then try your formula and check if it still have the issue.

In addition, do you mind share your sample data table for further analysis?

Best Regards,
Angelia

[chanchanha]

@v-huizhn-msft,

Training[End_Date] is the "End_Date" Column on the "Training" Table.  My End_Date column contains future dates, which i don't it display on the table when PowerBi refreshes daily.  Can you elaborate a little more on your idea of calendar table?

Thanks for looking into this!  Let me know if you have any quesitons.

Attendee_Count	Training_ID	Start_Date	End_Date	Venue_City	Venue_State	Venue_Zip
5	61997	1/10/2017	1/10/2017	Ainsworth	Nebraska	69210
32	59398	1/10/2017	1/10/2017	Albany	New York	12208
14	62532	1/6/2017	1/6/2017	Albemarle	North Carolina	28001
5	59831	1/9/2017	1/10/2017	Alton	Illinois	62002
24	61883	1/30/2017	1/30/2017	Ambridge	Pennsylvania	15003
6	61681	2/3/2017	2/3/2017	Ames	Iowa	50010
1	53656	4/20/2017	4/20/2017	Ames	Iowa	50010
11	62563	1/20/2017	1/20/2017	Andover	Minnesota	55304
10	63096	1/11/2017	1/12/2017	Ann Arbor	Michigan	48105
17	61171	2/8/2017	2/15/2017	Aptos	California	95003
21	63197	1/26/2017	1/26/2017	Aransas Pass	Texas	78336
3	64035	5/13/2017	5/13/2017	Marysville	California	95901
15	61643	2/17/2017	2/17/2017	Arlington	Texas	76054
35	61995	1/10/2017	1/10/2017	Ashland	Oregon	97520
6	65048	2/17/2017	2/17/2017	Atlanta	Georgia	30303
21	63124	1/26/2017	1/26/2017	aurora	Colorado	80012
12	63853	2/1/2017	2/1/2017	Aurora	Colorado	80012
11	65037	2/21/2017	2/21/2017	Aurora	Colorado	80011
18	64716	2/17/2017	2/17/2017	Austin	Texas	78758
21	62932	1/26/2017	1/26/2017	Ballwin	Missouri	63021
16	61325	2/13/2017	2/13/2017	Bel Air	Maryland	21014
19	60764	2/22/2017	2/23/2017	Bellevue	Washington	98007
20	63923	1/18/2017	1/18/2017	Bellflower	California	90706
20	64124	2/4/2017	2/5/2017	Berkeley	California	94704
23	64527	2/11/2017	2/11/2017	Berkeley	California	94720

[v-huizhn-msft]

Hi @chanchanha,

Please list your expected result based on your given sample data. So that I can analysis clearly.

For create a Calendar table, you can use Calendar function to create a DateTime table, and relate it to your factial table.

CALENDAR(<start_date>, <end_date>) 

Best Regards,
Angelia