Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
chanchanha
New Member

DAX expression to display data associated with past dates

 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?

3 REPLIES 3
v-huizhn-msft
Employee
Employee

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

@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_CountTraining_IDStart_DateEnd_DateVenue_CityVenue_StateVenue_Zip
5619971/10/20171/10/2017AinsworthNebraska69210
32593981/10/20171/10/2017AlbanyNew York12208
14625321/6/20171/6/2017AlbemarleNorth Carolina28001
5598311/9/20171/10/2017AltonIllinois62002
24618831/30/20171/30/2017AmbridgePennsylvania15003
6616812/3/20172/3/2017AmesIowa50010
1536564/20/20174/20/2017AmesIowa50010
11625631/20/20171/20/2017AndoverMinnesota55304
10630961/11/20171/12/2017Ann Arbor Michigan48105
17611712/8/20172/15/2017AptosCalifornia95003
21631971/26/20171/26/2017Aransas PassTexas78336
3640355/13/20175/13/2017MarysvilleCalifornia95901
15616432/17/20172/17/2017ArlingtonTexas76054
35619951/10/20171/10/2017Ashland Oregon97520
6650482/17/20172/17/2017AtlantaGeorgia30303
21631241/26/20171/26/2017auroraColorado80012
12638532/1/20172/1/2017AuroraColorado80012
11650372/21/20172/21/2017AuroraColorado80011
18647162/17/20172/17/2017AustinTexas78758
21629321/26/20171/26/2017BallwinMissouri63021
16613252/13/20172/13/2017Bel AirMaryland21014
19607642/22/20172/23/2017BellevueWashington98007
20639231/18/20171/18/2017BellflowerCalifornia90706
20641242/4/20172/5/2017BerkeleyCalifornia94704
23645272/11/20172/11/2017BerkeleyCalifornia94720

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.