Sum values for each Distinct value

[Anonymous]

Hi, 

I'm trying to create a measure that returns the total number of weeks that are missing per person since the last date of their data upload, and then to sum all into a total value.

For example: 

 

I created the following measure: 

Missing Weeks from last Update = CALCULATE(DISTINCTCOUNT(Calendar[Fiscal Week]), DATESBETWEEN(Calendar[Fiscal Week],LASTDATE('Table1'[Date])+1, TODAY())) * DISTINCTCOUNT('Table1'[Person ID])

That does the job well when it consider every case separately, for example on the left table presented above, it tells me how many weeks are missing per person, thats what I need and I want to sum all those missing weeks. But when it is summarized, obviously it just takes the last date of the table and returns 3. I would like for the measure to return the complete number, which in that example, would be 17, instead of 3. 

Please help on how  I could do this. 

Thanks

[pranit828]

HI @Anonymous 

try

CALCULATE(SUM(DATEDIFF(Today(), 'Table1'[Date], WEEK )),ALLXCEPT('table','Table'[Person ID]))

[pranit828]

Hi @Anonymous 

Can you please provide more info with screenshots, input and required output in table format for the community so that your ask can be better understood and resolved quickly.

From what I understand unless I see the input data.

CALCULATE(SUMX(DATEDIFF(Today(),calander[date], WEEK )-[diff in calander week and fiscal week]),ALLXCEPT('table','Table'[Person ID]))

[Anonymous]

Sure @pranit828 , I'll try to explain my self better. 

I've got a table with records from users who upload records every week. What I need to know is, how many weeks are missing on the data in total since the last update per user. 

 Another example, my measure knows the number of missing weeks per person. But I need the sum of all those missing weeks. 

Here's my current measure, i need for it to know the last date on the data of each person, calculate how many weeks are missing per each, and finally summing all of those. 

Missing Weeks from last Update = CALCULATE(DISTINCTCOUNT(Calendar[Fiscal Week]), DATESBETWEEN(Calendar[Fiscal Week],LASTDATE('Table1'[Actual Date]), TODAY())) * DISTINCTCOUNT('Table1'[Person ID])

Please tell me if you got any questions. 

[pranit828]

Hi @Anonymous 

This issue seems like the column data type issue.

Make sure it is set to whole number.

and the calulateion for that column is set to sum, in your case I believe it is set to max.

[Anonymous]

Hi @pranit828  I believe it's something more. 

I have it formatted as whole number

And that option of SUM isn't available.

Despite, I use the table for explanation. What I really need is for the complete number of missing weeks to appear in a card. 

Like this:

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

This is a measure totals problem. Very common. See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

For your case, you need create a new measure like below:

m_Total 1 = 
VAR __table = SUMMARIZE('Table1',[Resource Name],"__value",[Missing Weeks from last Update])
RETURN
SUMX(__table,[__value])

Mow use this new measure in the visual.

Regards,

Lin

[Anonymous]

Hi @pranit828 , 

Thanks for your reply, but that didn't work. Also I dont want the normal calendar week, I want my fiscal year week which has already being calculated on the Calendar Table. 

The formula return the error: The SUM function only accepts a column reference as an argument.

Regards,