Reply
Frequent Visitor
Posts: 5
Registered: ‎02-07-2018

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.

 

 

 

 

Community Support Team
Posts: 7,441
Registered: ‎08-14-2016

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: | |
Frequent Visitor
Posts: 5
Registered: ‎02-07-2018

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
Posts: 7,441
Registered: ‎08-14-2016

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: | |
Frequent Visitor
Posts: 5
Registered: ‎02-07-2018

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
Posts: 7,441
Registered: ‎08-14-2016

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
Posts: 3,918
Registered: ‎01-14-2017

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
Frequent Visitor
Posts: 5
Registered: ‎02-07-2018

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
Posts: 3,918
Registered: ‎01-14-2017

Re: countrows YTD

Hi,

 

That link requires me to sign in.