Calculating Year to Date Based On a Condition

[Atanas_Atanasov]

Dear community, 

I am struggling with an YTD calculation. 

I need to calculate [# of leads] YTD only when [number of lead generators] > 0.

Tried few different codes and this is what I came up with (not displaying the result I expected).

What I expect is to see 25 + 18 + 15 and this is the code I used:

Test =

SUMX(

    VALUES( 'Calendar'[Month & Year] ),

    IF( [Number Of Lead Generators] > 0,

    CALCULATE( [# Of Leads], DATESYTD( 'Calendar'[Date], "Oct-31" ))))

Can anyone help me with that. 

Thank you in advance, 

Atanas

[Atanas_Atanasov · Accepted solution]

@johnt75 - I made it!

I created Measure Test with this code:

Test =

VAR something = FILTER(

                    ADDCOLUMNS(VALUES('Calendar'[Month & Year]),

                        "@ # Of Leads", CALCULATE([# Of Leads]),

                        "@ Nbr of Lead Generators", CALCULATE([Number of Lead Generators])

                        )

                    , NOT ISBLANK([@ Nbr of Lead Generators])

                    )

VAR something_YTD = CALCULATE(SUMX(something, [@ # Of Leads]), DATESYTD('Calendar'[Date], "Oct-31"))

RETURN

something_YTD

Then I created another measure Correct Result with this code:

Correct Result =

Correct Result =

IF( [# Of Leads] = BLANK(), BLANK(),

CALCULATE(

    [Test],

    DATESYTD( 'Calendar'[Date], "Oct-31" )))

And now I have the correct result

Thank you for your help and wih you all the best, 

Atanas

[Atanas_Atanasov · Accepted solution]

@johnt75 - I made it!

I created Measure Test with this code:

Test =

VAR something = FILTER(

                    ADDCOLUMNS(VALUES('Calendar'[Month & Year]),

                        "@ # Of Leads", CALCULATE([# Of Leads]),

                        "@ Nbr of Lead Generators", CALCULATE([Number of Lead Generators])

                        )

                    , NOT ISBLANK([@ Nbr of Lead Generators])

                    )

VAR something_YTD = CALCULATE(SUMX(something, [@ # Of Leads]), DATESYTD('Calendar'[Date], "Oct-31"))

RETURN

something_YTD

Then I created another measure Correct Result with this code:

Correct Result =

Correct Result =

IF( [# Of Leads] = BLANK(), BLANK(),

CALCULATE(

    [Test],

    DATESYTD( 'Calendar'[Date], "Oct-31" )))

And now I have the correct result

Thank you for your help and wih you all the best, 

Atanas

[johnt75]

Try

Test =
VAR AllMonths =
    CALCULATETABLE (
        VALUES ( 'Calendar'[Month & Year] ),
        DATESYTD ( 'Calendar'[Date], "Oct-31" )
    )
VAR ValidMonths =
    FILTER ( AllMonths, [Number Of Lead Generators] > 0 )
VAR Result =
    SUMX ( ValidMonths, [# Of Leads] )
RETURN
    Result

[Atanas_Atanasov]

I've tried it, but this is the result I got:

[johnt75]

I think

Test =
VAR AllMonths =
    CALCULATETABLE (
        VALUES ( 'Calendar'[Month & Year] ),
        DATESYTD ( 'Calendar'[Date], "Oct-31" )
    )
VAR ValidMonths =
    FILTER ( AllMonths, [Number Of Lead Generators] > 0 )
VAR Result =
    CALCULATE ( [# Of Leads], REMOVEFILTERS ( 'Calendar' ), ValidMonths )
RETURN
    Result

should do it

[Atanas_Atanasov]

Same result unfortunately. 

I'd expect to see 43 in Jul, but the code gets me 18 😞

[johnt75]

Can you change the measure to first return the COUNTROWS of AllMonths and then the COUNTROWS of ValidMonths? Just want to identify which bit isn't working as intended.

[Atanas_Atanasov]

Dear @johnt75 

This is what I get when I return the count Rows of AllMonths:

And this is what I get when I return the countrows of ValidMonths:

Thank you one more time. 

PS: I am getting a bit desperate on that calculation 😞

Atanas

[johnt75 · Accepted solution]

I think I know what the problem is. Your Year Month column is using a different column as the sort by column, correct? Try

Test =
VAR AllMonths =
    CALCULATETABLE (
        SUMMARIZE (
            'Calendar',
            'Calendar'[Month & Year],
            'Calendar'[Year month sort column]
        ),
        DATESYTD ( 'Calendar'[Date], "Oct-31" )
    )
VAR ValidMonths =
    FILTER ( AllMonths, [Number Of Lead Generators] > 0 )
VAR Result =
    CALCULATE ( [# Of Leads], REMOVEFILTERS ( 'Calendar' ), ValidMonths )
RETURN
    Result

[Atanas_Atanasov]

@johnt75  - That is 100% true. My month and year column is sorted by another column in order to be shown chronologically, and not alphabetically. 

Your code worked flawlessly this time around:

Thank you so much for your help!

It is deeply appreciated!

All due respect, 

Atanas