YTD Across multiple previous years based on latest date

[Anonymous]

Hi all,

I'm trying to replicate the action of this combined chart and slicer in a measure.

Essentially I want my measure to sum units to month from the start of the year, but to look for the maximum date (month) in the dataset and apply that time restraint to all previous years e.g. 2008 units = those between Jan and May 2008, 2009 units = those between Jan & May 2009 etc.

I can achieve this by defining the month I want my units to be summed i.e <= May, but my data is refreshed weekly so I'd like it to dynamically measure units each time we reach a new month in the data. 

It would be a SAMEPERIODLASTYEAR() but over all previous years, and I've tried out several variations of ALL() and FILTER() against a MAX([Month]), but it always only applies the MAX([Month]) against the final year in the dataset as all other years contain dates right up to 31st December.

Thanks for any insight anyone can provide.

[v-lili6-msft]

hi, @Anonymous 

Just add conditional that year=max(year) when applies the MAX([Month]).

If you still have the problem, please share some simple sample data and expected output.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,

Lin

[Anonymous]

Hi @v-lili6-msft thanks for your reponse.

Here is some sample data

Each date represents a transaction. The final date in the series is in May 2019 and so I would like my measure [Transaction Count (YTD)] to show all previous years transactions only to May also. This is shown on the left hand charts which interact with the filter to show the desired result. My measure is in the right hand chart where i've tried to include a filter that would only count transactions that occured in a month less than the max month. I have tried to add in a max year too, but I'm not too sure where to include it

Transaction Count (YTD) = CALCULATE([Transaction Count],FILTER(ALL(Transactions[Date]),Transactions[Date]<=[Max Date] && YEAR([Date])=[Max Year]))

[v-lili6-msft]

hi, @Anonymous 

 My measure is in the right hand chart where i've tried to include a filter that would only count transactions that occured in a month less than the max month.

What is your expected output? Do you mean that, if you select "Month (Number)" is 1-10, and the every year only calculate month before 5? because 2019 only have data until May, so other year should also calculate until may even if others year have data in other month.

Best Regards,

Lin

[Anonymous]

@v-lili6-msft I'm trying to replicate the chart on the left, which is using a filter, in the right hand chart which doesn't use a filter. I am trying to make the measure detect the final month in my data (in this instance May) and only count transactions up to that month but for all previous years too.

[CheenuSing · Accepted solution]

Hi@Anonymous ,

Based on the file provided by you I changed the measure

1.  Max Month = Calculate(MONTH([Max Date]),ALL(Transactions))

2. Transaction Count (YTD) = CALCULATE([Transaction Count],

                             FILTER(ALL(Transactions[Month (Number)]),

                               Transactions[Month (Number)]<=[Max Month] ))

With these two changes the barchart on both sides is same.

Check it out.

Cheers

CheenuSing

[v-lili6-msft]

hi, @Anonymous 

Ok, you could use this way to get it:

Step1:

Add a year-month column

Yearmonth = Transactions[Year]*100+Transactions[Month (Number)]

Step2:

Just use these two measure to get it.

MaxMon No = CALCULATE(MAX(Transactions[Month (Number)]),FILTER(ALL(Transactions),Transactions[Yearmonth]= CALCULATE(MAX(Transactions[Yearmonth]),ALL(Transactions))))

Result = CALCULATE([Transaction Count],FILTER(Transactions,Transactions[Month (Number)]<=[MaxMon No]))

Result:

and here is pbix file, please try it.

Best Regards,

Lin