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: | |
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.

Highlighted
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
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 255 members 2,723 guests
Please welcome our newest community members: