Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
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
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 |
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |