cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sohan
Helper III
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?

1 REPLY 1
amitchandak
Super User
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))

Helpful resources

Announcements
Vote for T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!