YTD Calculation

[Anonymous]

Hi,

I have Data Like Below.In the data i have 3 years of data and for 2019 i have only upto Oct, values but i have Months Nov & Dec as null .Now i need to calculate YTD which is only upto Oct 2019.Please help with calculation

Year    Month    Value

2017      Jan          2

2017      Feb         3

2017      Mar        4

2017      Apr         5

2017      May         6

2017      Jun          7

2017      Jul           8

2017      Aug         9

2017      Sep         10

2017      Oct         11

2017      Nov         12

2017      Dec         2

2018      Jan          2

2018      Feb         3

2018      Mar        4

2018      Apr         5

2018      May         6

2018      Jun          7

2018      Jul           8

2018      Aug         9

2018      Sep         10

2018      Oct         11

2018      Nov         12

2018      Dec         2

2019      Jan          2

2019      Feb         3

2019      Mar        4

2019      Apr         5

2019      May         6

2019      Jun          7

2019      Jul           8

2019      Aug         9

2019      Sep         10

2019      Oct         11

2019      Nov         -

2019      Dec         -

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

For your case "calculate YTD which is only upto Oct 2019", you could try one of these three measures:

Step1:

Add a Month No column and  a Year-Month column

Step2:

Just try this measure formula

Measure 1 = CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Year]=MAX('Table'[Year])&&'Table'[Month No]<=MAX('Table'[Month No])))

or this logic:

Measure 2 = 
var _maxyearmonth=CALCULATE(MAX('Table'[Year-Month]),FILTER(ALL('Table'),'Table'[Value]<>BLANK())) return
var _maxmonthnoofmaxyear=CALCULATE(MAX('Table'[Month No]),FILTER(ALL('Table'),'Table'[Year-Month]=_maxyearmonth)) return
IF(MAX('Table'[Month No])<=_maxmonthnoofmaxyear, CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Year]=MAX('Table'[Year])&&'Table'[Month No]<=MAX('Table'[Month No]))))

or this ogic:

Measure 3 = 
var _maxyearmonth=CALCULATE(MAX('Table'[Year-Month]),FILTER(ALL('Table'),'Table'[Value]<>BLANK())) return
IF(MAX('Table'[Year-Month])<=_maxyearmonth, CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Year]=MAX('Table'[Year])&&'Table'[Month No]<=MAX('Table'[Month No]))))

Result:

and here is sample pbix file, please try it.

Regards,

Lin

[amitchandak]

Create a date column in your table. And create date dimension. And use datesytd or totaltyd

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date])))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year)))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year))))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601