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:  n...
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.

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.