Forum Discussion

amitchandak's avatar
amitchandak
Super User
5 years ago

HenryJS , Have a month or week table with dates . No need to join

And use measures like this for these dates

MOT expires measure = minx(filter(table,[MOT expires] <=Date[Date] && [Service Due] >= Date[Date]),[MOT expires] )
Service Due measure = maxx(filter(table,[MOT expires] <=Date[Date] && [Service Due] >= Date[Date]),[Service Due] )

 

The week on column   and Vehicle Reg  on row

 

Calendar with week

https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482

HenryJS's avatar
HenryJS
Post Prodigy
5 years ago

amitchandak it did not work with the measure you sent - please see below

 

MOT expires measure = minx(filter('Vehicle Fleet Data',[MOT expires] <= 'calendar'[Date] && 'Vehicle Fleet Data'[Estimate Month Service Due] >= 'calendar'[Date]),'Vehicle Fleet Data'[MOT expires] )
 
  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    HenryJS , My mistake

    Try like

    MOT expires measure = minx(filter(table,table[MOT expires] <=max(Date[Date]) && table[Service Due] >= max(Date[Date])),table[MOT expires] )
    Service Due measure = maxx(filter(table,table[MOT expires] <=max(Date[Date]) && table[Service Due] >= max(Date[Date])),table[Service Due] )