Target Measure doesn't use current month, uses appended query

[michael_knight]

Hi,

I'm trying to create a target measure. I've got two targets which are the same.

The black line target in a formula target based on a numbers within the system, the red target is manually given to me at the start of each month and used for the duration of the month until there is enough data in the system. 

I want to create a measure that uses the black line for every single month apart from the current month, and use the red line for only the current month

Any help would be appriciated

If you have any question please don't hesitate to ask

Thanks,

Mike

[v-eachen-msft]

Hi @michael_knight ,

Do you mean to create a single line with these two lines?

You could use IF() and SELECTEDVALUE() functions to judge if it is current month. If it returns TRUE, you could use the measure of red line. If it returns FALSE, you could use the measure of blank line.

[michael_knight]

Hi @v-eachen-msft ,

Yes I do want them in the same line

Sorry, I'm still learning DAX. I tried with the code below but I have had no luck. 

IsCurrentMonth = 
IF( 
    SELECTEDVALUE(Appointment[createdon].[Date]) =MONTH(NOW(),[Target], 'Targets- WBAH'[WBAH Target]))

The theory I want is

If current month, return 'Target', else return 'WBAH Target' 

Any suggestions

Thanks,

Mike

[v-eachen-msft]

Hi @michael_knight ,

You could try the following DAX:

IsCurrentMonth =
IF (
    SELECTEDVALUE ( MONTH ( Appointment[createdon] ) ) = MONTH ( NOW () ),
    [Target],
    SELECTEDVALUE ( 'Targets- WBAH'[WBAH Target] )
)

All values you return need to be measure.

[michael_knight]

Hi @v-eachen-msft 

Give it another go and still no luck. I tweeked your code a bit to make it work and the measure itself is working now, but it isn't showing what I need. It's returning 'Targets' throughout rather than only showing 'Targets' for the current month 

IsCurrentMonth = 
IF (
    SELECTEDVALUE ( 'Full Date'[Full Date]) = MONTH ( NOW () ),
    [Target],
    SELECTEDVALUE('Targets- WBAH'[Target], 'Targets- WBAH'[WBAH Target]) ) 

Thanks,

Mike

[v-eachen-msft]

Hi @michael_knight ,

You need to add month() function as MONTH(SELECTEDVALUE('Full Date'[Full Date])). According to your situation, the month is the key to comparison. If you want to compare more carefully, you could refer to the following DAX:

IsCurrentMonth = 
IF (
    MONTH ( SELECTEDVALUE ('Full Date'[Full Date])) = MONTH ( NOW () )
 && YEAR ( SELECTEDVALUE ('Full Date'[Full Date])) = YEAR ( NOW () ),
    [Target],
    SELECTEDVALUE('Targets- WBAH'[Target], 'Targets- WBAH'[WBAH Target]) ) 

[michael_knight]

Hi @v-eachen-msft ,

Thanks for the help. I've still had no luck. It shows the values that I want to show just the current month for every month 

I am going to include the PBIX file if you want to see what I'm playing around with, it might make it easier.

https://www.dropbox.com/s/02mxxdtyu5hon77/Acquisitions%20-%20Community%202.pbix?dl=0

Thanks,

Mike