YTD per calendar year

[NutNick1982]

Hello Fellow Power BI Users!

I need help with a calculation that I tried and just haven’t been able to get right. On the table below I have a 3 construction jobs that each finish at different times. On the first month (and first month only) that a job is 100% complete, the ‘First month 100%’ column will indicate ‘yes’. I want to do a YTD for all the ‘Yes’ for each year. The YTD measure I currently have does not reset back to zero and start the count again at the beginning of a new calendar year.  

Table:

Measure that I need help with:

I appreciate any help you might be able to provide.

[amitchandak]

@NutNick1982 , create the calculation of yes, that it should give 1 and 0.

The use datesytd.

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

if need at values(Date[Dates])

https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

[NutNick1982]

Thanks for your response, I appreciate it. The changing of 'First Month 100%' makes sense and I did so:

However the measure still isnt giving the result I want. Here's how I input:

And the corresponding output for the measure:

The "YTD Sales" should maybe more like a TOTALYTD? For example, if one Job gets to 100% the Total YTD should be 1 there after until the new year or when anothe job reaches 100%. Something like this:

Thanks for your help!

[v-kelly-msft]

Hi @NutNick1982 ，

Not quite understand your output?Is "YTD Sales" your expected output?How does it happen?

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[NutNick1982]

Hi,  the "YTD sales" should be a culumative sum by year. It should culumatively count the "first  month 100%"  column then on the start of a new calendar year, restart that count.

[v-kelly-msft]

Hi @NutNick1982 ，

Create a column as below:

YTD Sales = 
var _prefirstmonth=CALCULATE(MAX('Table'[First Month 100%]),FILTER('Table','Table'[Date]<=EARLIER('Table'[Date])&&YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))))
var _count=CALCULATE(COUNT('Table'[First Month 100%]),FILTER('Table',YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))))
Return
IF(_prefirstmonth=0,0,IF('Table'[First Month 100%]=1||_prefirstmonth=1,
IF(_count=1,1,
IF(_count>1,
SUMX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&&'Table'[Date]<=EARLIER('Table'[Date])),'Table'[First Month 100%])))))

And you will see:

For the related .pbix file,pls see attached.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[NutNick1982]

Hi Kelly, It looks like this is the solution! does it matter that the 'First month 100%' is a measure and not a column? additionally your BI file doesnt seem to be opening....