Measure Total is Blank

[maxabele]

Hi all,

I searched the problem but nothing worked for this case.

I calculate turnover in a specified date range. The measure shows how many people left during that time. The formula works as it should, but the total is blank. That's the formula (effective date means date of last work day):

Staff_Turnover = IFERROR(Calculate(Countrows(Worker_Data);

Filter(Values(Worker_Data[Effective Date]);'Worker_Data'[Effective Date]<=Min('Date'[Date]));

Worker_Data[Effective Date]<>BLANK());BLANK())

Any help is greatly appreciated.

Thanks in advance and best,

Max

[v-juanli-msft · Accepted solution]

Hi @maxabele 

As tested, it works on my side. 

No relationship for two tables.

Staff_Turnover =
IFERROR (
    CALCULATE (
        COUNTROWS ( Worker_Data ),
        FILTER (
            VALUES ( Worker_Data[Effective Date] ),
            'Worker_Data'[Effective Date] < MIN ( 'Date'[Date] )
        ),
        Worker_Data[Effective Date] <> BLANK ()
    ),
    BLANK ()
)


or
Measure =
IFERROR (
    CALCULATE (
        DISTINCTCOUNT ( Worker_Data[worker id] ),
        FILTER (
            Worker_Data,
            [Effective Date] <> BLANK ()
                && [Effective Date] < [mindate]
        )
    ),
    BLANK ()
)

Please make sure you turn on the "total" option,

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[maxabele]

Thanks, Maggie! Works perfectly! 😀

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.

[d_gosbell]

So your issue is due to your filter statement. At the grand total level the Min('Date'[Date]) expression will return the first date in your date table and at that time noone will have left. One simple fix might be to switch this to use MAX() although that will change the nature of the measure at aggreate levels to show everyone that had left as at the end of the period (eg end of the month/quarter/year etc).

[maxabele]

Thanks for the replies! 

When I use Max instead of Min, Total returns the highest turnover that happened in the year. Any idea how I could change that to the sum of all turnover values? 

[amitchandak]

<> Blank, I doubt it will work like that.

Refer to this blog post. consider termination join and remove start date filter

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[kentyler]

You probably need to break this down in smaller pieces to debug it.

taff_Turnover = IFERROR(Calculate(Countrows(Worker_Data);

Filter(Values(Worker_Data[Effective Date]);'Worker_Data'[Effective Date]<=Min('Date'[Date]));

Worker_Data[Effective Date]<>BLANK());BLANK())

try using VARs

VAR minDate = Min('Date'[Date])

then return MinDate to see if its what you think it is

or return "error" instead of BLANK for IFERROR

see whether your blanks are because of errors