End of month

[siljewiehauge]

Hello. I'm working on a report in PBI desktop. It's a dataset with the company's contracts. I have a Start Date and and End Date. 

I've used the this measure.

AktiveKontrakterIgjen = VAR tmpTabell = SELECTCOLUMNS(FILTER(GENERATE('#Net'; 'Date'); [Date] >= [Start Date] && [Date] <= [RiktigEndDate]); "ID"; [Index]; "Date"; [Date]) VAR tmpTabell1 = GROUPBY(tmpTabell; [ID]; "Count"; COUNTX(CURRENTGROUP(); [Date])) RETURN COUNTROWS(tmpTabell1)​

I do get the outcome I want PER DAY. How can I get the number correct for the EndofMonth? 

I tried to add an ENDofMonth Column to the Calendar table, but it still gives me the sum (or I'm not even sure what the number is when I look at week number or month in the Date hierarcy). How can this be solved? 

In addition - this is a measure that is quite heavy for the dataset I'm working on. It takes a long time to calculate, as we have active contracts today that started in 2002. Would really appreciate any help 🙂 

[siljewiehauge]

Here's the outcome of the code. For 4 of March - correct. For Week 10 or for the month March, not correct. 

[Anonymous]

First, the measure is not correct syntactically. Open www.daxformatter.com and try to format it. You won't be able.

Second, the measure is certainly too complex for the task at hand, which means its calculation is ineffective and hence slow.

If you explain carefully what it is you want and what the model looks like, you may get an answer.

Please get familiar with this:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best
D