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

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

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

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
PowerBiDD
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 IV
Super User IV

Re: countrows YTD

Hi,

 

That link requires me to sign in.


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

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors