cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerBiDD Frequent Visitor
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
Community Support Team
Community Support Team

Re: countrows YTD

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
PowerBiDD Frequent Visitor
Frequent Visitor

Re: countrows YTD

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

Community Support Team
Community Support Team

Re: countrows YTD

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Highlighted
PowerBiDD Frequent Visitor
Frequent Visitor

Re: countrows YTD

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

Community Support Team
Community Support Team

Re: countrows YTD

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Super User
Super User

Re: countrows YTD

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.

PowerBiDD Frequent Visitor
Frequent Visitor

Re: countrows YTD

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.

Super User
Super User

Re: countrows YTD

Hi,

 

That link requires me to sign in.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 516 members 4,094 guests
Please welcome our newest community members: