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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Atanas_Atanasov
Helper II
Helper II

Calculating Year to Date Based On a Condition

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, 
AtanasCapture.JPG

 

2 ACCEPTED SOLUTIONS
Atanas_Atanasov
Helper II
Helper II

@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
Atanas_Atanasov_2-1692877651330.png

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

Atanas

View solution in original post

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

View solution in original post

9 REPLIES 9
Atanas_Atanasov
Helper II
Helper II

@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
Atanas_Atanasov_2-1692877651330.png

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

Atanas

johnt75
Super User
Super User

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

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

Atanas_Atanasov_0-1692869004104.png

 

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

Same result unfortunately. 

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

Atanas_Atanasov_0-1692869793420.png

 

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.

Dear @johnt75 

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

Atanas_Atanasov_0-1692876515899.png

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

Atanas_Atanasov_1-1692876727510.png

Thank you one more time. 

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

Atanas

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

@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:

Atanas_Atanasov_0-1692881551357.png

Thank you so much for your help!

It is deeply appreciated!

All due respect, 

Atanas

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.