Filter used to calculate measure affecting calculated column

[Anonymous]

Hi,

At my company we have financial months and I am trying to work out how many working days are remaining in the current month. The measures work prefectly by themselves but not when put into a calculated column.

My measures are:

Days in Current Month = LOOKUPVALUE(Dates[Work Days],Dates[Date], today())

Days so far month = CALCULATE(sum(Dates[Work Day]), 
FILTER(Dates,Dates[Date]< today()), 
FILTER(Dates,Dates[Month] = [Current Month]), 
FILTER(Dates,Dates[Year] = [Current Year]))

My calculated column is:

Days Remaining T = 

if(or(and(Dates[Month] < LOOKUPVALUE(Dates[Month],Dates[Date], TODAY()),(Dates[Year] = LOOKUPVALUE(Dates[Year],Dates[Date], TODAY()))), (Dates[Year] < LOOKUPVALUE(Dates[Year],Dates[Date], TODAY()))), 0,
if(or(and(Dates[Month] > LOOKUPVALUE(Dates[Month],Dates[Date], TODAY()),(Dates[Year] = LOOKUPVALUE(Dates[Year],Dates[Date], TODAY()))), (Dates[Year] > LOOKUPVALUE(Dates[Year],Dates[Date], TODAY()))),Dates[Work Days],



if(and(Dates[Month] = LOOKUPVALUE(Dates[Month],Dates[Date], TODAY()),(Dates[Year] = LOOKUPVALUE(Dates[Year],Dates[Date], TODAY()))), [Days in Current Month]-[Days so far month],
123456789)))

The top two IF statements work great and are for dates that are in past or future months. The final one is causing me grief.

If I were to hard code 17 (as of 07 March 2019 there are 17 days remaining) in it puts 17 in every row, that is what I want, for the current month but with the measures working it out it seems to remove 1 from the working days for the motnh for every working day completed.

Any help would be greatly appreciated.

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

To create a calcualted column in dates table firstly.

YearMonthf = YEAR('Dates'[Date])*100+MONTH('Dates'[Date])

After we can create measures as below.

Current YearMonth = YEAR(TODAY())*100+ MONTH(TODAY())

Days in Current Month = LOOKUPVALUE(Dates[Work Days],Dates[Date], today())

Days so far month = CALCULATE(sum(Dates[Work Day]), 
FILTER(ALL(Dates),Dates[Date]< today()&& Dates[YearMonthf]=[Current YearMonth])) 

Then we can create the result column based on them.

Days Remaining T = IF([Current YearMonth]<Dates[YearMonthf],0,IF([Current YearMonth]>=Dates[YearMonthf] && Dates[Date]>=TODAY(),[Days in Current Month]-[Days so far month],0))

Regards,

Frank

[v-frfei-msft]

Hi @Anonymous ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Regards,
Frank