cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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))

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors