Find previous and next date

Hi, I hope somebody can help me with this setup I want to achieve in PowerBI. I've 2 tables, a table tblServers & a table tblWaves. The tblServers lists out all servers & to which wave they bel...

Need Help

Anonymous

2 years ago

Hi svendaems ,

Thanks for your concern about this case!
svendaems Have you solved your problem? If not, please try this way:
Because today is not May 3rd, so I use May 3rd to test:

Previous Patching = 
VAR PatchingName = tblPatching[Name]
VAR RelatedWave = RELATED(tblServers[Wave])
VAR TargetDate = CALCULATE(
    MINX(
        FILTER(
            tblWaves,
            tblWaves[Wave] = RelatedWave &&
            tblWaves[Date] <= DATE(2024, 5, 3)
        ),
        tblWaves[Date]
    ),
    ALL(tblWaves)
)
RETURN TargetDate

Next Patching = 
VAR PatchingName = tblPatching[Name]
VAR RelatedWave = RELATED(tblServers[Wave])
VAR TargetDate = CALCULATE(
    MINX(
        FILTER(
            tblWaves,
            tblWaves[Wave] = RelatedWave &&
            tblWaves[Date] >= DATE(2024, 5, 3)
        ),
        tblWaves[Date]
    ),
    ALL(tblWaves)
)
RETURN TargetDate

Please change the

DATE(2024, 5, 3)

into

TODAY()

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Find previous and next date.pbix85 KB

Greg_Deckler

Community Champion

2 years ago

svendaems Can you post your data as text? I also don't understand how you have a 1 to many relationship between those two tables. This *may* be it::

Previous Patching = 
  VAR __Today = TODAY()
  VAR __Wave = MAX('tblServers'[Wave])
  VAR __Result = MAXX(FILTETER('tblWaves', [Wave] = __Wave && [Date] <= __Today), [Date])
RETURN
  __Result

Next Patching = 
  VAR __Today = TODAY()
  VAR __Result = MINX(FILTETER('tblWaves', [Wave] = __Wave && [Date] >= __Today), [Date])
RETURN
  __Result