Help with CALCULATE in Measure

DAX DAX Calculations
1

I’m sure this question is similar to a hundred other questions that have been asked, but until I can make sense of my specific example, I’m struggling to get my head around this. I wish I could upload a PBIX file, but due to data security in my organization, I can’t. However, I will try to describe the situation a best I can.

I am trying to provide a report with info regarding completion of online training courses. I have three tables:

  1. Courses
  2. Completions
  3. Dates

The Dates table goes from 1/1/2014 to 12/31/2020. There is a one-to-many relationship between Dates[Date] and Completions[Completion Date]. I am trying to create a measure that will show the total number of completions for the current year and then display the totals by month.

I have created the following measures:

Total Completions = SUM(Completions[Completions])
YTD Completions = CALCULATE([Total Completions], Dates[Year] = YEAR(TODAY()))

When I put the Dates[Year,] Dates[Month], and YTD Completions into column in a table, I get the following:

2014 Jan <Jan 2019 Total>
2014 Feb <Feb 2019 Total>

… and so on, all the way through Dec 2020 with each month always showing the corresponding 2019 totals.

What do I need to change in order for my data to only show rows for each month in 2019?

Thanks in advance, and my apologies for the lack of a sample file. I hope I have adequately described the problem. If I need to be more clear about anything, please let me know.

I’m sure this question is similar to a hundred other questions that have been asked, but until I can make sense of my specific example, I’m struggling to get my head around this. I wish I could upload a PBIX file, but due to data security in my organization, I can’t. However, I will try to describe the situation a best I can.

I am trying to provide a report with info regarding completion of online training courses. I have three tables:

  1. Courses
  2. Completions
  3. Dates

The Dates table goes from 1/1/2014 to 12/31/2020. There is a one-to-many relationship between Dates[Date] and Completions[Completion Date]. I am trying to create a measure that will show the total number of completions for the current year and then display the totals by month.

I have created the following measures:

Total Completions = SUM(Completions[Completions])
YTD Completions = CALCULATE([Total Completions], Dates[Year] = YEAR(TODAY()))

When I put the Dates[Year,] Dates[Month], and YTD Completions into column in a table, I get the following:

2014 Jan <Jan 2019 Total>
2014 Feb <Feb 2019 Total>

… and so on, all the way through Dec 2020 with each month always showing the corresponding 2019 totals.

What do I need to change in order for my data to only show rows for each month in 2019?

Thanks in advance, and my apologies for the lack of a sample file. I hope I have adequately described the problem. If I need to be more clear about anything, please let me know.

2

@DaveC,

Would you be able to create and send a PBIX composed of entirely fictitious data, showing the results you are getting, and the results you want to get. I feel if we had that to look at, this would be a quick solve.

Thanks.

  • Brian
3

can you try this
YTD = TOTALYTD([Total Completions], Dates[Month])

4

Yes you need to use time intelligence functions here.

Either DATESTYD or TOTALYTD.

See below for a comprehensive review of time intelligence.

http://portal.enterprisedna.co/courses/mastering-dax-calculations/lectures/2000656

Sam

1 Like