Forum Discussion

mojekk's avatar
mojekk
Frequent Visitor
3 years ago
Solved

Select Specific Date and Previous 6 Months Data

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!

 

DATESALES
31/01/2021453
31/01/2021605
31/01/2021867
28/02/2021724
28/02/2021490
28/02/2021275
31/03/2021875
31/03/2021338
31/03/2021458
30/04/2021269
30/04/2021560
30/04/2021623
31/05/2021753
31/05/2021492
31/05/2021327
30/06/2021377
30/06/2021654
30/06/2021669
31/07/2021636
31/07/2021702
31/07/2021208
31/08/2021405
31/08/2021449
31/08/2021495
30/09/2021386
30/09/2021822
30/09/2021384
31/10/2021486
31/10/2021326
31/10/2021210
30/11/2021458
30/11/2021766
30/11/2021460
31/12/2021271
31/12/2021622
31/12/2021208
31/01/2022540
31/01/2022455
31/01/2022325
28/02/2022650
28/02/2022208
28/02/2022226
31/03/2022260
31/03/2022579
31/03/2022550
30/04/2022578
30/04/2022480
30/04/2022667
31/05/2022301
31/05/2022336
31/05/2022427
30/06/2022676
30/06/2022835
30/06/2022461
31/07/2022351
31/07/2022230
31/07/2022465
31/08/2022392
31/08/2022375
31/08/2022425
30/09/2022346
30/09/2022612
30/09/2022268
31/10/2022420
31/10/2022377
31/10/2022488
30/11/2022407
30/11/2022812
30/11/2022363
DAX Command
DAX Commands
Measures
  • v-yadongf-msft's avatar
    v-yadongf-msft
    3 years ago

    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.

5 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

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

    • mojekk's avatar
      mojekk
      Frequent Visitor
      3 years ago

      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's avatar
        FreemanZ
        Icon for Super User rankSuper User
        3 years ago

        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. 

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    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

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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.