Why monthoffset is not working?

[baray098]

I am having a hard time understanding why monthoffset working for every kpi except one.

Context:
I am trying to build a kpi card of 5 measures. Active opportunity, Win Rate, Deals Closed, Conversion Rate and Total Revenue for month. My dataset has 4 tables: Accounts, Products, Sales Pipelines, Sales team. I have a datetable created with active relationship with Close date and a inactive relationship with engage date. I created all formulas and for the monthly value, I used monthoffset value in the filters pane. But it give me all correct values except active opportunity. For active oppor. I activated the inactive relationship with the following formula:

Active Opportunities = 
CALCULATE(
    COUNTROWS(sales_pipeline),
    sales_pipeline[deal_stage] IN {"Prospecting", "Engaging"},
    USERELATIONSHIP(DateTable[Date], sales_pipeline[engage_date])
)

it gave the incorrect result for dec. But it give correct values for nov, oct and sept.

my datetable formula is:

DateTable = 
ADDCOLUMNS(
    CALENDAR(MIN(sales_pipeline[engage_date]), MAX(sales_pipeline[close_date])),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMM"),
    "MonthNo", MONTH([Date]),
    "Quarter", "Q" & FORMAT([Date], "Q"),
    "Year-Month", FORMAT([Date], "MMM YYYY"),
    "Weekday", FORMAT([Date], "dddd"),
    "IsWeekend", IF(WEEKDAY([Date],2)>5, TRUE(), FALSE()),
    "MonthOffset", DATEDIFF(CALCULATE(MAX(sales_pipeline[close_date])), [Date], MONTH)
)​

It giving correct value if manually filter month December and year 2017. But I want to know why this is happening. I sharing the pbix file. If anyone can explain the issue, it would be helpful. 

File link:

Sales Opportunity.pbix

[Ahmedx]

pls try this code

Active Opportunities = 
CALCULATE(
    COUNT(sales_pipeline[engage_date]),
    sales_pipeline[deal_stage] IN {"Prospecting", "Engaging"},
    USERELATIONSHIP(DateTable[Date], sales_pipeline[engage_date]) 
)

 

[danextian · Accepted solution]

Hi @baray098 

I'm not sure what exactly your expected result is as you only mentioned just  the correct result. But assuming that your expected value is 18 for Dec 2017, it is possibly because when Power BI auto-generates the DAX, it tries to summarize sales_pipeline[Active Opportunities] grouped by DateTable, but those unmatched rows will still show up as blanks in DateTable column so those 500 rows will be added unless explicitly filtered out. This seems to be the case only if the filter value is 0.

Try the following:

Active Opportunities =
CALCULATE (
    COUNTROWS ( sales_pipeline ),
    sales_pipeline[deal_stage] IN { "Prospecting", "Engaging" },
    USERELATIONSHIP ( DateTable[Date], sales_pipeline[engage_date] ),
    KEEPFILTERS ( NOT ( ISBLANK ( DateTable[Date] ) ) )
)

[baray098]

Thanks. Now I understand what is going on. Can you tell me whether I should remove the rows with blank values? What is best practice? I am assuming that would create incorrect results for other metrics.

[danextian]

It depends on whether you have a need for those rows. Do you intend to show the number of records or track those that don't have either of the two date columns? If yes, then keep them.

[speedramps]

You have not provided example of your Calendar table.

Perhaps you defined the monthoffset incorrectly.

Click here to dowbload a working example form onedrive
Click here 

Please click thumbs up because I have tried to help you.

Then click accept solution if it fixes the problem.

I want to help you more but your description is too vague. 

You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming

* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow. ‌‌
* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble. ‌‌
* Please click the thumbs up button for these helpful hints and tips. Thank you.

Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.

Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.