Forum Discussion

baray098's avatar
baray098
Frequent Visitor
1 year ago
Solved

Why monthoffset is not working?

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, Convers...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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] ) ) )
)

     

     

danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

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's avatar
baray098
Frequent Visitor
1 year ago

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's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    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.