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

October 28 & 29: Experts share their secrets on how to pass the Fabric Analytics Engineer certification exam—live. Learn more

Reply
PowerBiDD
Frequent Visitor

countrows YTD

Hello,

 

I'm very new to powerbi and to this forum. My apologies upfront if I place this in the wrong section or if the question has been answered already. Even with the help of likewise posted messages I can't get my measure to work.

 

I have a fairly easy dataset consisting of 3 tables.

 

- A download of all incoming mails in 2017

- A download of all incoming mails in 2018

- A table with unique dates, months, month-names and year

 

I have created a column chart with an easy count function by month and now I wish to make a YTD chart in which the number of emails are adding up per month. In both mail tables there is a date received column, so there should be an easy way to count these, but I don't seem to get it done.

 

My best effort wa:

YTD 2018 = CALCULATE(COUNTX(Mail2018;DATESYTD(Mail2018[Received])))

 

Hope you can help me with this.

 

Thanks a lot.

 

 

 

 

8 REPLIES 8
v-shex-msft
Community Support
Community Support

HI @PowerBiDD,

 

Please share some sample data to help use clarify your data structure.(please do mask on sensitive data before sharing)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi Xiaoxin,

 

I hope you can open the following link. In the line chart is now the number of received emails by month. I would like to make that a YTD chart.

 

 link to pbix file

 

Many thanks in advance.

 

Regards

HI @PowerBiDD,

 

I can't view your report from hyperlink. Maybe you can share some sample data/pbix file for testing.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi Xiaoxin

 

I don't know how I can add a file to my reply, so I am sharing a photo of the dashboard and one of the raw data.

 

The lower visuals should be a year-to-date line, but it's obviously not.

 

I hope this helps you.

 

Many thanks

 

example dashboard.pngexample raw data.png

Hi,

 

I would suggest the following:

 

  1. Append both mail datasets into a single one
  2. Create a relationship from the Date column appearing in the appended dataset to the date column in your Calendar Table
  3. In your visual drag Months from the Calendar Table to the X - axis
  4. Drag the Year (from the Calendar Table) to the slicer and select any one year
  5. Write this measure

=CALCULATE(COUNTROWS(Data),DATESYTD(Calendar[Date]))

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello guys,

 

thanks for your patience and helps.

 

I have copied the URL from my onedrive below. I have combined the 2017 and 2018 mails into a single table.

 

dashboard received e-mails

 

@Ashish_Mathur: as you can see in the dashboard, I get an error message when I'm trying to produce your measure. Besides that I have also another question. Instead of creating filters per year, is het also possible to drag "Year" to the legend field. If I'm right then I should get 2 lines (one for each year), right?

 

Many thanks again.

Hi,

 

That link requires me to sign in.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

HI @PowerBiDD,

 

You can upload it to onedirve/google drive, then share link here.

BTW, if you store records in different tables, I don't think you can direct use time intelligence functions to get previous from other tables.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

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