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.
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
)
)
)
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?
@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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |