Forum Discussion

svendaems's avatar
svendaems
Frequent Visitor
2 years ago
Solved

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's avatar
    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.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
2 years ago

svendaems Try:

Previous Patching = 
  VAR __Today = TODAY()
  VAR __Result = MAXX(FILTETER('tblWaves', [Date] <= __Today), [Date])
RETURN
  __Result

Next Patching = 
  VAR __Today = TODAY()
  VAR __Result = MINX(FILTETER('tblWaves', [Date] >= __Today), [Date])
RETURN
  __Result
svendaems's avatar
svendaems
Frequent Visitor
2 years ago

Hi Greg_Deckler 

 

Thanks already for your reply. Unfortunlatly it's not working out as it should. It isn't looking at the related server, but giving for all servers the same date. It takes the first date that matches the formula (first previous date or first next date, related to today).

 

 

  • Greg_Deckler's avatar
    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
    • svendaems's avatar
      svendaems
      Frequent Visitor
      2 years ago

      Hi Greg_Deckler 

       

      You're right, I made a mistake in my question. It's a many to many relationship. I've added the data in a test pbx file, you can download it here.

       

      I've tried also your second suggestion but that gives the same result.