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

     

     

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

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