Forum Discussion
YTD Calculation
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 -
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
2 Replies
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-functionsAppreciate 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-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601hi 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
