countrows YTD

[PowerBiDD]

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.

[v-shex-msft]

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

[PowerBiDD]

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

[v-shex-msft]

HI @PowerBiDD,

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

Regards,

Xiaoxin Sheng

[PowerBiDD]

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

[Ashish_Mathur]

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]

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.

[Ashish_Mathur]

Hi,

That link requires me to sign in.

[v-shex-msft]

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