Calculate YTD using monthly Pass/Fail (1, 0) values

[pjandliz]

Hi

I have a table with Actual and Target values by Month, Client, Person and have created a measure to indicate whether Actual > Target (ie 'pass', or target met) or not, using the following:

Bill Tgt Met:=IF(OR(ISBLANK([Bill Act]), ISBLANK([Bill Tgt])), BLANK(), IF([Bill Act] >= [Bill Tgt], 1, 0))

(I've had to use the ISBLANK function to  avoid getting records with blank/null values appearing in a matrix table visual).

I am now trying to calculate a YTD count of the passes, but am stuck, so would greatly appreciate any help you could provide.

I've been trying to use the TOTALYTD function to sum the monthly values for Pass in Month, but I just seem to be getting either a 1 or 0.

My YTD formula is:  TotalYTD(IF(ISBLANK([Bill Tgt Met]), BLANK(), [Bill Tgt Met]), tblDateTable[Month End], "6/30")

I suspect my formula is picking up all of the records for Actual and Target within the YTD period, and giving me the Actual v Target outcome for this rather than determining the 'Pass' fpr each month in the YTD date range ad totalling them.

ie  If Jan, Feb, Mar are all passes (1) and Apr is a fail (0), then my YTD Apr should be 3.

Many thanks.

[Phil_Seamark]

Hi @pjandliz

Have you tried using your first measure inside your YTD measure?

Something like....

My YTD formula :=   
    TotalYTD(
            [Bill Tgt Met], 
            tblDateTable[Month End], 
            "6/30"
            )

[pjandliz]

Hi Phil

Yes, that was my first choice and when that didn't work I resorted to dropping the actual formula in alas to no avail 8-(

[pjandliz]

On revisiting this today, I've realised I'm going about this the wrong way (I'm a DAX Dummy!).

Let me re-specify the situation and requirement.

Hi

My table has a whole bunch of records by Month, Client, Person, each with Actual and Target billing values (amongst other things) and I've created a measure to indicate whether Actual > Target (ie 'pass', or Bill Tgt Met) or not, using the following:

       Bill Tgt Met:=IF(OR(ISBLANK([Bill Act]), ISBLANK([Bill Tgt])), BLANK(), IF([Bill Act] >= [Bill Tgt], 1, 0))

where Bill Act and Bill Tgt are from the tblNIDOData table (mentioned below in the YTD formula)

(I've had to use the ISBLANK function to  avoid getting records with blank/null values appearing in a matrix table visual).

My visual uses filters for Person & Month End (date) to select which month and/or person I want to show, including the Bill Tgt Met for the selected month, and for the YTD. The YTD is the count of the number of months within the YTD the billing target is met.

I'm calculating Bill Tgt Met as either 1 (Pass) or 0 (Fail), so I can sum these values for each of the months within the current YTD (based on the filter's selected Month End).

So, I need to somehow use a single Month End selected in a filter and using this work out which month ends make up the YTD (at that month end), then for each of these months calculate the Bill Tgt Met measure, then finally total these and return the result.

eg  As my Year ends 30 June, then when Dec is selected, I need to calculate the Bill Tgt Met for Jul (say, Pass = 1), Aug (1), Sep (1), Oct (Fail = 0), Nov (0) & Dec (1), so YTD Dec should be 4. For YTD Sep, Oct & Nov they all end up as 3.

A colleague has suggested using something like this - which uses an alternate date table ('tblDateTableAlt' same as the main date table 'tblDateTable') with an inactive link to the source fact table 'tblNIDOData' - however I think it's still only giving me the Pass/Fail for the last month in the YTD.

Bill Tgt Met YTD:=CALCULATE(
CALCULATE(TOTALYTD([Bill Tgt Met],
'tblDateTable'[Month End],
ALL('tblDateTable'[Month End]),
"6/30"),
ALL('tblDateTable')),
FILTER(
VALUES('tblDateTableAlt'[Month End]),
(EOMONTH(LASTDATE('tblDateTable'[Month End]),
-IF(MONTH(LASTDATE('tblDateTable'[Month End]))<7,
MONTH(LASTDATE('tblDateTable'[Month End]))+6,
MONTH(LASTDATE('tblDateTable'[Month End])-6))
) <= 'tblDateTableAlt'[Month End]
&&
'tblDateTableAlt'[Month End] <= LASTDATE('tblDateTable'[Month End])
)
),

//use the inactive alternative date table relationship

USERELATIONSHIP(
'tblNIDOData'[Month End],
'tblDateTableAlt'[Month End])
)

All my dates are month ends, and the Month End filter on the page uses the main Date table's Month End column.

Clear as mud ?!?

I hope this better explains my problem so I can find a solution before ending up in the loony bin 

Thanks Paul

[v-yuta-msft]

Hi pjandliz,

Based on your description, the slicer is based on tblDateTable, right?

To achieve your requirement, you can refer to DAX formula below:

My YTD formula =

CALCULATE (

    [Bill Tgt Met],

    FILTER (

        tblDateTable,

        tblDateTable[Month End] <= SELECTEDVALUE ( tblDateTable[Month End] )

            && tblDateTable[Month End] > "2017/6/30"

    )

)

Regards,

Jimmy Tao

[pjandliz]

Thanks Jimmy,

I tried using your formula but it turns out - as we're using the Desktop October 2017 version - SELECTEDVALUE isn't available to me.

That said, I think the formula will just calculate a single value of Bill Tgt Met based on data matching the filtered dates, whereas I need the formula to calculate the Bill Tgt Met value for each month of the year to date individually then add them all up.

All my efforts so far calculate the Bill Tgt Met value for the selected month. I'm begining to think this sort of calc is beyond the capabilities of DAX at the moment.

I can generate a chart showing each of the individual values, but not a consolidated total. Sigh!