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.

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.