Forum Discussion

Jsummersgill86's avatar
Jsummersgill86
New Member
1 year ago
Solved

DAX date issue - earliest date and record

Hi,   I've been out of day to day Power BI building for a couple of years but a project has come across my desk which should be pretty quick and easy. I have a very small data model, with a central...
  • Jsummersgill86's avatar
    Jsummersgill86
    1 year ago

    Hi Dinesh, I've managed to resolve this issue. I actually ended up reworking the data model a little bit which I meant I could leverage some of the out of the box time intelligence functions a bit more easily.

     

    Thanks

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
1 year ago

Jsummersgill86 Since you have Office ID in the visual, your First release date should just be:

First release date = MIN( 'DIM_DATE'[Date] )

Then your ITD_Estimated Insurance Payment_First should be:

ITD_Estimated Insurance Payment_First =
  VAR __Table = FILTER( 'Fact Table', [Date] = [First release date] )
  VAR __Result = SUMX( __Table, [Est_rev] )
RETURN
  __Result

 

  • Jsummersgill86's avatar
    Jsummersgill86
    New Member
    1 year ago

    Thanks for the response Greg.

     

    Given that my visual is filtered with a relative date slicer wouldn't MIN('DIM_DATE'[Date]) return the earliest date per the slicer? Rather than the earliest date from the fact table for each specific OfficeID?

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      1 year ago

      Jsummersgill86 Ah, misunderstand that ask. That would be:

      First release date = MINX( ALL( 'DIM_DATE' ), [Date] )
      • Jsummersgill86's avatar
        Jsummersgill86
        New Member
        1 year ago

        I think I'm getting even more confused than I was at the start. When I used your proposed 

        First release date = MINX( ALL( 'DIM_DATE' ), [Date] )

         

        My table visual gets blown out when I add this to it. For those Rev estimates occuring in my slicer date range I have a release date equal to the very first date in my Date table (not the first date for that specific office ID estimate). My visual then also then starts including all other office IDs (even if they haven't had an estimate in the slicer range).