Forum Discussion

susannataylor's avatar
susannataylor
Regular Visitor
6 years ago
Solved

Display Only Most Recent Value?

Hi all, hoping you can help me out! I'm still pretty new to Power BI and despite finding formulas that seem like the solution I'm still stumped!   Two of the progams I report outcomes on have waitl...
Need Help
  • edhans's avatar
    edhans
    6 years ago

    susannataylor ,
    This will give you the latest date in your table:

     

     

    Latest Date = 
CALCULATE(
    MAX('Table'[Week Of]),
    ALL('Table'[Week Of])
)

     

     

    This will give you the latest value for that date. You didn't specify what you meant by total. If you just mean the total of everything, then stick this measure in a card:

     

     

    Grand Total = SUM(Table[PATs Waitlist])

     

     

    But if you want the total for the latest date, then this works fo rthe PATs Waitlist column.

     

     

    Latest Total = 
CALCULATE(
    SUM('Table'[PATs Waitlist]),
    ALL('Table'[PATs Waitlist]),
    FILTER(
        ALL('Table'[Week Of]),
        'Table'[Week Of] = [Latest Date]
    )
)

     

     

     Can you explain the logic of your desired matrix? 

    EDIT: I looked at it again, and think I see what you mean. You need to fix your table in Power Query first.

    1. Select the first column (week of) in Power Query.
    2. On the Transform menu, select Unpivot Other Columns.
    3. Rename the columns as desired. You will get this:

      1.  

    4. Now create these two measures:

     

    Normalized Latest Date = 
CALCULATE(
    MAX('Normalized Table'[Week Of]),
    ALL('Normalized Table'[Week Of])
)
    Normalized Latest Week Total = 
CALCULATE(
    SUM('Normalized Table'[Value]),
    FILTER(
        ALL('Normalized Table'[Week Of]),
        'Normalized Table'[Week Of] = [Normalized Latest Date]
    )
)

     

    You can create this matrix:

    See my PBIX file here. You want the "Normalized Table" to work through.

mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

You can use an approach like this to get your result

 

Latest PATs Waitlist =
VAR maxdate =
    MAX ( Table[WeekOf] )
RETURN
    CALCULATE ( MIN ( Table[PATs Waitlist] ), Table[WeekOf] = maxdate )

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

  • susannataylor's avatar
    susannataylor
    Regular Visitor
    6 years ago

    mahoneypat 

    This worked great for just one column (PATs) but when I tried to add the FB column this error message popped up: "A circular dependency was detected: ECS Referrals PR[FB Most Recent Waitlist], ECS Referrals PR[PATs Most Recent Waitlist], ECS Referrals PR[FB Most Recent Waitlist]."

     

    Any ideas?