If current month, apply one formula, if not, use another

[Anonymous]

Hi everyone,

I am basically trying to get working days to date, but if it's current month, i need it to show working days till yesterday (exclude today). 

I am trying to use the below formula, but the bolded part gives me an error saying a single value cannot be determined (The Data File is basically when employees submit something everyday, so there are multiple of each dates):

Working days to date =

IF(

MONTH('DataFile'[Date]) = MONTH(TODAY()),

CALCULATE(

SUM('Date Table'[Work day]),

KEEPFILTERS('Date Table'[Date]<= TODAY())

) -1,

CALCULATE(

SUM('Date Table'[Work day]),

KEEPFILTERS('Date Table'[Date]<= TODAY())

)

)

Is this doable? This measure will need to be integrated into another measure to get compliance, so it needs to be a measure not a column. 

Thank you in advance! 

[Anonymous]

Hi  @Anonymous ,

You can modify the formula as follows and add a max:

IF(
MONTH(MAX('DataFile'[Date])) = MONTH(TODAY()),
CALCULATE(
SUM('Date Table'[Work day]),
KEEPFILTERS('Date Table'[Date]<= TODAY())
) -1,
CALCULATE(
SUM('Date Table'[Work day]),
KEEPFILTERS('Date Table'[Date]<= TODAY())
)
)

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Watsky · Accepted solution]

Hey @Anonymous ,

How about this?

Working days to date = 
VAR a = CALCULATE(SUM('Date Table'[Workday]),MONTH('DataFile'[Date])=MONTH(TODAY()),KEEPFILTERS('Date Table'[Date]<=TODAY()))
VAR b = SELECTEDVALUE('DataFile'[Date])
RETURN
IF(MONTH(b)=MONTH(TODAY()),a-1,a)

[Anonymous]

This worked, thank you!!! 

[Anonymous]

This works for current month, but when i filter to previous month, it wont work 😞 i have a card to show this formula, and it remains the same regardless of whta I select in the month slicer. 

[Watsky]

Can you give an example of what the result should look like?