Select Specific Date and Previous 6 Months Data

[mojekk]

I am trying to create a line chart that returns the previous year December data and the last 6 months data for current year.

I have managed to create the dax measure for last 6 months as below: 

nMth = 
VAR MAX_DATE = MAX('CALENDAR'[DATE])
VAR PREV_DATE = DATESINPERIOD('CALENDAR II'[DATE],MAX_DATE,-6,MONTH)
VAR RESULT = CALCULATE(SUM(TABLE[SALES]),
                REMOVEFILTERS('CALENDAR'),
                KEEPFILTERS(PREV_DATE),
                USERELATIONSHIP('CALENDAR'[DATE],'CALENDAR II'[DATE]))
RETURN RESULT

Now I am stuck on how to include the December data for previous year.

The mechanic of the visualisation is user will click on Date Slicer for single specific date, i.e., user select 30/11/2022, then it will show a line chart for 31/12/2021, 30/6/2022, 31/7/2022, 31/8/2022, 30/9/2022, 31/10/2022 and 30/11/2022. 

Any help is greatly appreciated!

DATE	SALES
31/01/2021	453
31/01/2021	605
31/01/2021	867
28/02/2021	724
28/02/2021	490
28/02/2021	275
31/03/2021	875
31/03/2021	338
31/03/2021	458
30/04/2021	269
30/04/2021	560
30/04/2021	623
31/05/2021	753
31/05/2021	492
31/05/2021	327
30/06/2021	377
30/06/2021	654
30/06/2021	669
31/07/2021	636
31/07/2021	702
31/07/2021	208
31/08/2021	405
31/08/2021	449
31/08/2021	495
30/09/2021	386
30/09/2021	822
30/09/2021	384
31/10/2021	486
31/10/2021	326
31/10/2021	210
30/11/2021	458
30/11/2021	766
30/11/2021	460
31/12/2021	271
31/12/2021	622
31/12/2021	208
31/01/2022	540
31/01/2022	455
31/01/2022	325
28/02/2022	650
28/02/2022	208
28/02/2022	226
31/03/2022	260
31/03/2022	579
31/03/2022	550
30/04/2022	578
30/04/2022	480
30/04/2022	667
31/05/2022	301
31/05/2022	336
31/05/2022	427
30/06/2022	676
30/06/2022	835
30/06/2022	461
31/07/2022	351
31/07/2022	230
31/07/2022	465
31/08/2022	392
31/08/2022	375
31/08/2022	425
30/09/2022	346
30/09/2022	612
30/09/2022	268
31/10/2022	420
31/10/2022	377
31/10/2022	488
30/11/2022	407
30/11/2022	812
30/11/2022	363

[v-yadongf-msft · Accepted solution]

Hi @mojekk ,

Create a calendar table:

CalendarTable = ADDCOLUMNS(CALENDAR(MIN('Table'[DATE]),MAX('Table'[DATE])),"Year",YEAR([Date]))

Create a measure:

Measure = 
var _date = SELECTEDVALUE('CalendarTable'[Date])
var _dateperiod = DATESINPERIOD('CalendarTable'[Date],_date,-6,MONTH)
var last_6_month = IF(YEAR(_date) = YEAR(TODAY()),CALCULATE(SUM('Table'[SALES]),_dateperiod))
var previous_year_December = CALCULATE(SUM('Table'[SALES]),FILTER(ALL('CalendarTable'),'CalendarTable'[Year] = 2021 && MONTH('CalendarTable'[Date]) =12))
return
last_6_month + previous_year_December

I think you will get the result you want:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@mojekk , if you just want to show total of last 6 months, then you can use a measure like

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-6,MONTH))

Rolling Months Formula: https://youtu.be/GS5O4G81fww

But if you want to display trend, then you need an independent table

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -6) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

Need of an Independent Table in Power BI - Exclude: https://youtu.be/lOEW-YUrAbE

[FreemanZ]

what will be shown if 30/9/2022 is picked from slicer?

[mojekk]

If 30/9/2022 is selected it will show 31/12/2021, 30/4/2022, 31/5/2022, 31/6/2022, 30/7/2022, 31/8/2022 and 30/9/2022 data. I forgot to mention that whatever date is chose in slicer it will always display the 31/12/2021 followed by previous 6 months of selected date.

[FreemanZ]

hi @mojekk 

this is not viable, as the x axis can't be responsive to the slicer, because

1) only columns could be put in x axis

2) and columns are not responsive to the slicer.