cancel
Showing results for
Did you mean:  Helper III

## Measure only showing 6 months in the past

I have the following two measures:

``````Measure 1.1 =

CALCULATE(DISTINCTCOUNT(FactAppointment[AppointmentID]),
DATESINPERIOD(FactAppointment[Date appointment], TODAY(), 6, MONTH))``````

``````Measure 1.2 =

IF(
MAX(DimCalendar[Date]) <= TODAY(),
CALCULATE(
SUMX( ALLSELECTED(DimCalendar[Date]), [Measure 1.1] ),
DATESINPERIOD(
DimCalendar[Date],
MAX(DimCalendar[Date]),
6,
MONTH
)
)
)``````

• Measure 1.1 counts the number of appointments in the coming 6 months.
• Measure 1.2 (should) give(s) the result for every date in the date calendar.

The results only show from 6 months in the past however. How can I ensure that the result in measure 1.2 is shown for all dates in the date calendar?  Super User

@Sohan , if you want show last 6 month data as dates on axis/row/group by

Rolling  6 Sales =
var _max = maxx(allselected(date),date[date]) // or today()
var _min = date(year(_max), month(_max)-6,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(date, date[date] <=_max && date[date] >=_min))

But if you want to select 1 day/month and want to get 6 month trend, then the slicer need to be on an independent table

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

//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(CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH)) , filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))  