Month by Month filtering where previous years total remains the same

[Anonymous]

Hi there, 

I'm very new to Power BI. I am looking for the best way to filter monthly figures for the current year of my dataset (2018) against a summation of all years previous. I have over 10 years worth of data, and would like to isolate this year's monthly figures against the summation of all figures prior to be enable drilldown for my visuals on the dashboard. 

From the sample data below, the columns I am using are Employee Number and Start Date. 

BG	Employee Number	Service Line	Function	Department	Age	Gender	Work Location	Job	Grade	Length Of Service	Start Date
UK	123				25						01/02/1999
UK	1234				57						02/03/2000
UK	12345				45						04/05/2000
UK	123456				65						10/12/2001
UK	1234567				23						25/02/2003
UK	1234567				45						04/07/2003
UK	1234567				45						08/09/2003
UK	1234567				65						01/02/2004
UK	1234567				23						05/06/2006
UK	1234567				34						08/10/2006
UK	1234567				35						01/05/2007
UK	1234567				36						01/08/2008
UK	1234567				44						06/08/2009
UK	1234567				55						07/08/2010
UK	1234567				57						06/07/2012
UK	1234567				22						06/09/2015
UK	1234567				45						01/01/2018
UK	1234567				61						03/04/2018
UK	1234567				21						05/06/2018

 From this, I would like to display the total number of employees on my dashboard. I would like to use a month by month filter to show month changes of 2018 against all years previous. 

In more detail, i would like to be able to filter say the figures for January 2018, these figures would be added to the total figures for all years prior.  Or for February and March 2018, which would exclude January and the subsequent months for 2018 against the total figures for all years prior. 

Here's the relationship pane for the dataset: 

Is this something that can be done?

Thanks in advance!

[v-yuta-msft]

Hi alby,

"In more detail, i would like to be able to filter say the figures for January 2018, these figures would be added to the total figures for all years prior. Or for February and March 2018, which would exclude January and the subsequent months for 2018 against the total figures for all years prior. "

<--- Could you give some expected results and share more details about the logic of how to achieve the total amount all previous year? Do you have some filter condition like based on some slicers?

Regards,

Jimmy Tao

[Anonymous]

Hi,

I have a list of 5 State Financial years ranging from 2013 to 2018. I am able to calculate difference between two consecutive years(2017,2018) and difference between current year 2018 with any previous year but not able to calculate difference for previous years like (2015,2017)

The DAX functions I used here are:

YTD= Total YTD([Value], (Date), “2018/6/30”)
PYTD = Calculate([YTD], Datesbetween(Date), Date(2013,7,1), Date(2017,6,30)))

Can someone assist me here? Thanks!

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[Anonymous]

Thanks for the reply. I did try that but somehow it did not work for my dataset or maybe I did not do it correctly, Can you please let me know if my DAX formulas are correct?

YTD= Total YTD([Value], (Date column), “2018/6/30”)
PYTD = Calculate([YTD], Datesbetween(Date column), Date(2013,7,1), Date(2017,6,30)))

[v-yuta-msft]

Hi rohitvungarala,

Have you solved your issue? If you have, could you kindly mark my answer?

Regards,

Jimmy Tao

[v-yuta-msft]

Hi rohitvungarala,

To compare values between 2018 and 2015, you should achieve total value in 2015 and 2018 separately,  you can modify your formula like below and check if it can meet your requirement. 

Value in 2015 = Calculate([YTD], Datesbetween(Date column), Date(2014, 6, 31), Date(2015, 6, 30)))

Regards,

Jimmy Tao

[Greg_Deckler]

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