Sum of a daily measure at a monthly level

[Turf03]

I have a measure that creates a score at the daily level for employees. I would like to sum the daily measure at the monthly level but when attempting to do so, it bases score on the monthly totals.

I dont know what I'm missing here???

[edhans]

You are going to need to share some data using the links below, and show expected output. This has to do with the filter being applied, but without some info, it is vitually impossible to determine your specific issue.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Turf03]

Basically every day, depending on employee performance, they receive a points for meeting all of their targets.

The points column is a measure Points = If(Met Targets = "Yes",1,0)

Whats happening, if I create a table that is a monthly wrap up, looking at the total monthly column (Met Targets) and then giving them points based on that instead of the total month.

So based on the sample below, instead of 2 points for January, its giving the employee 1 point.

Date	Employee	Met Targets	Points
1/1/2020	Bob	Yes	1
1/2/2020	Bob	Yes	1
1/3/2020	Bob	No	0

[edhans · Accepted solution]

Hi @Turf03 - I think this is a modeling issue. I just set this up and it just works. Let me walk you through what I did.

1. I created a date table. I use this one. Full instructions there on how to get that into Power Query. But you will need to change the first Source line to this:
   1. = {Number.From(#date(2020,1,1))..Number.From(#date(2020,12,31))}
   2. If you want to make it dynamic so it moves in time with new data you get, see this article. Creating a Dynamic Date Table in Power Query
2. I marked it as a Date Table in the desktop. Just right-click on it in the Report view, Mark as Date Table, Mark as Date Table, then select the date column.
   1. 
   2. Turn off Automatic date/time intelligence in Options. You have a real date table now and do not need the mini-date tables Power BI generates.
3. Ensure the date table is related to your data table. It should be a 1 to many from the date table to your table with the filter pointing to your table.
   1. 
4. Added a new measure for total points:
   1. Total Points = SUM('Table'[Points])
5. Created a table and dropped the "Month Name" field from the Date table, Employee name, and Total Points measure.

That gave me the above image that shows Bob with 2 points.

Here is my PBIX file if you want to play with it.