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.

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