Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
pjandliz
Advocate II
Advocate II

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:

 

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.

 

5 REPLIES 5
Phil_Seamark
Microsoft Employee
Microsoft Employee

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"
            )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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-(

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 Man Tongue

 

Thanks Paul

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

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!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.