Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

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:   Bi...
v-yuta-msft's avatar
v-yuta-msft
Icon for Community Support rankCommunity Support
8 years ago

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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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!