Forum Discussion
Variable Measures?
is the select month in a filter on the report page.
if so can you try changing the ALL('DimDate') to ALL('SelectMonth')
NETWORK DAYS 2017_variable = CALCULATE(COUNTROWS('DimDate'),ALL('SelectMonth'), FILTER('DimDate', 'DimDate'[Day of Week Number] < 6),DATESBETWEEN(DimDate[Date],DATE(2017,[MonthNumSelect],1), DATE(2017,[MonthNumSelect],[EndofMonthday])))
although I'm still not entirely sure I follow.... and no doubt am probably frustrating you further
no frusteration at all. this report has sent me sideways.
these might explain it
NETWORK DAYS 2017_variable2 and 3 are the two formulas you provided.
When nothing is selected they all show the default value for april (first in the list and with 20 days) with the exception of the one which is sorted by month number so 21 days in january.
They are all slightly different formulas but essientially functionally identical.
one slicer is for dimdate and the other is for selectmonth
ok... just so I can have it clear in my head.
of the 3 screenshots you sent which if the outputs are wrong and what should they be?
also to save me going backwards and forwards over the screens on here would you mind including hte measure text of those wrong ones please?
Thanks
D
the first two are wrong or atleast show error. Because when they are filtered to march they should show 23 network days which they do. as well if its not filtered it should show the default number of network days, which they do (21 or 20 depending on if its alphabetical order april=20 or numerical january=21).
The error is with the 'WFP YTD Ratio 2017' it should equal the number to the left of it (0.881)
NETWORK DAYS 2017_variable2 = CALCULATE(COUNTROWS('DimDate'),ALL('DimDate'), FILTER('DimDate', 'DimDate'[Day of Week Number] < 6),DATESBETWEEN(DimDate[Date],DATE(2017,[MonthNumSelect],1), DATE(2017,[MonthNumSelect],[EndofMonthday])))
=23 (when filtered for march, otherwise 20 by default)
WFP YTD Ratio 2017 = IF('Cap Targets 2017'[WFP Flag2017]=1, 'Cap Targets 2017'[WFP Days2017]*8/[TOTAL_HOURS], IF('Cap Targets 2017'[WFP Flag2017]=0,(([NETWORK DAYS 2017_variable]*8)/[TOTAL_HOURS]), 0))
= .0766
heres the issue; if I change it and hardcode the network days in as 23 (the way it shows on the chart) it will return the correct cumber (0.881)
WFP YTD Ratio 2017 = IF('Cap Targets 2017'[WFP Flag2017]=1, 'Cap Targets 2017'[WFP Days2017]*8/[TOTAL_HOURS], IF('Cap Targets 2017'[WFP Flag2017]=0,(((23)*8)/[TOTAL_HOURS]), 0))
=0.881
(conversly if I hardcode it to 20 it will display 0.0766)
so for whatever reason when networkdays is nested in another measure it doesnt display the same number, just the default. It is not lost on me that it may be a function of my approach to monthnumselect:
MonthNumSelect = SWITCH( FIRSTNONBLANK(SelectMonth[Month], SelectMonth[Month]), "January", Month(Date(2017,1,1)), "February", MONTH(Date(2017,2,1)),"March", MONTH(Date(2017,3,1)), "April", MONTH(Date(2017,4,1)),"May", MONTH(Date(2017,5,1)), "June", MONTH(Date(2017,6,1)),"July", MONTH(Date(2017,7,1)), "August", MONTH(Date(2017,8,1)),"September", MONTH(Date(2017,9,1)), "October", MONTH(Date(2017,10,1)),"November", MONTH(Date(2017,11,1)), "December", MONTH(Date(2017,12,1)))
where in certain instances it doesnt acknowledge a filter? I say this as at one point I attempted to use if(hasonevalue as it would often return it as if it doesnt have one value (even though it was filtered)
Hope this answers any further questions I know it is confusing.
Hi,
out of interest what makes up the measure [EndofMonthday]
Thanks
EndofMonthday = DAY((EOMONTH(date(2017,[MonthNumSelect],1),0)))