Return sum of volume based on date in slicer

[Anonymous]

Hi All

I have a list of volume sold by Catelog number and Month, and want to sum the Year to Date (YTD) and Year to Go (YTG) of volume based on the date selected in the slicer. For example, when Jan 2021 is selected, the YTD shall return the sum of Oct Nov and Dec (business year in my case starts from Oct), and YTG returns the rest of the year.

A time table was created and related to it, and measures below were tried:

VolYTD=CALCULATE(SUM('VolSold'[Vol]), FILTER('TimeTable','TimeTable'[Month]<MAX('TimeTable'[Month])))

VolYTG=CALCULATE(SUM('VolSold'[Vol]), FILTER('TimeTable','TimeTable'[Month]>=MAX('TimeTable'[Month])))

The result is not right. When no month selected, VolYTD returns the sum from Oct to Aug, and VolYTG returns Sep. When any month selected, VolYTD will change to empty and VolYTG only shows the sum of the selected month.

Hope this is clear and look forward to any comments.

Thanks.

[amitchandak · Accepted solution]

@Anonymous , do you have date. If yes then use time intelligence and created ytd like example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"9/30")) // year ending in sep

You can create date with help from month year.

Ot create a month year table(separate) have numeric month and year. Make sure you year is same for oct-sep and Month 1 is oct.

Then you can have measure like

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Month] <= Max('Date'[Month]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Month] <= Max('Date'[Month])))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441

[Anonymous]

Thanks Amitchandak for this quick comments. DATESYTD is really something inspriring!

Given your expertise may I have 2 more questions:

1. I checked and see there is no date intelligience for YTG number, any suggestion on that? 

2. After get the YTD number I now need to have the monthly average. I tries AVERAGE function but found it calcualtes by cells not by month. For example, if a product line has 2 SKUs, then 3 months will yield 6 cells=>the result will be divided by 6 not 3. Any advices on this? Thanks and sorry for so many questions.