Forum Discussion
Month End Value
Hello,
I am having trouble with a formula to pull in the end of month value.
There are 4 columns in my spreadsheet
1) Portfolio ID
2) Period End
3) NAV
4) Run Date
I need to pull each period end (3/31/2022, 4/30/2022, 5/31/2022 etc.) which can be found in column 2. Note: I do not need the values for Period End 5/20/2022 or 4/22/2022 which is also on my report. Only last day of month. This report is run on a daily basis and the Run date can be found under Run Date column 4. (see my data below)
I need to pull the latest date (Run Date) period end NAV value. For example. the latest Run date you will see 3/31/2022 Period End date is on Run Date 5/13/2022 therefore that NAV value and period end date needs to pull into my table. And the latest Run date you see 4/30/2022 Period End is on Run Date 6/21/2022 therefore that NAV value and period end date needs to pull into my table. Lastly, the latest Run date you see 5/31/2022 Period End is on Run Date 6/24/2022 therefore that NAV value and period end date needs to pull into my table.
**This can change from day to day. For example, as of 6/25/2022 (tomorrow) run date the period end can go back to 4/30/2022 and then i would need that NAV value for that period end date since it is the latest
Port Id Period End NAV Run Date
| 76625321 | 3/31/2022 | 16,544,612 | 04/26/2022 |
| 76625321 | 3/31/2022 | 16,544,612 | 04/27/2022 |
| 76625321 | 3/31/2022 | 16,544,612 | 04/28/2022 |
| 76625321 | 4/22/2022 | 19,801,372 | 04/29/2022 |
| 76625321 | 4/22/2022 | 19,801,372 | 05/02/2022 |
| 76625321 | 4/22/2022 | 19,801,372 | 05/03/2022 |
| 76625321 | 3/31/2022 | 16,544,612 | 05/04/2022 |
| 76625321 | 3/31/2022 | 16,544,612 | 05/05/2022 |
| 76625321 | 4/30/2022 | 19,635,790 | 05/06/2022 |
| 76625321 | 4/30/2022 | 19,635,790 | 05/09/2022 |
| 76625321 | 4/30/2022 | 19,635,790 | 05/10/2022 |
| 76625321 | 4/30/2022 | 19,635,790 | 05/11/2022 |
| 76625321 | 3/31/2022 | 16,544,612 | 05/12/2022 |
| 76625321 | 3/31/2022 | 16,544,612 | 05/13/2022 |
| 76625321 | 5/31/2022 | 20,617,248 | 05/17/2022 |
| 76625321 | 5/31/2022 | 20,617,248 | 05/18/2022 |
| 76625321 | 5/31/2022 | 20,617,248 | 05/19/2022 |
| 76625321 | 5/31/2022 | 20,617,248 | 05/20/2022 |
| 76625321 | 5/31/2022 | 20,617,248 | 05/23/2022 |
| 76625321 | 4/30/2022 | 19,630,890 | 05/24/2022 |
| 76625321 | 4/30/2022 | 19,630,890 | 05/25/2022 |
| 76625321 | 5/20/2022 | 19,453,282 | 05/26/2022 |
| 76625321 | 5/20/2022 | 19,453,282 | 05/27/2022 |
| 76625321 | 5/20/2022 | 19,453,282 | 05/30/2022 |
| 76625321 | 5/20/2022 | 19,453,282 | 05/31/2022 |
| 76625321 | 4/30/2022 | 19,630,890 | 06/01/2022 |
| 76625321 | 4/30/2022 | 19,630,890 | 06/02/2022 |
| 76625321 | 4/30/2022 | 19,630,890 | 06/06/2022 |
| 76625321 | 4/30/2022 | 19,630,890 | 06/07/2022 |
| 76625321 | 4/30/2022 | 19,630,890 | 06/08/2022 |
| 76625321 | 5/31/2022 | 19,414,872 | 06/09/2022 |
| 76625321 | 5/31/2022 | 19,414,872 | 06/10/2022 |
| 76625321 | 5/31/2022 | 38,829,744 | 06/13/2022 |
| 76625321 | 4/30/2022 | 19,630,890 | 06/14/2022 |
| 76625321 | 4/30/2022 | 19,630,890 | 06/15/2022 |
| 76625321 | 4/30/2022 | 19,630,890 | 06/16/2022 |
| 76625321 | 4/30/2022 | 19,630,890 | 06/17/2022 |
| 76625321 | 4/30/2022 | 19,630,890 | 06/21/2022 |
| 76625321 | 5/31/2022 | 19,416,466 | 06/22/2022 |
| 76625321 | 5/31/2022 | 19,416,466 | 06/23/2022 |
| 76625321 | 5/31/2022 | 19,416,466 | 06/24/2022 |
Please see below for what my table/report should look like after the updates mentioned above...
| Period End | NAV |
| 3/31/2022 | 16,544,612 |
| 4/30/2022 | 19,630,890 |
| 5/31/2022 | 19,416,466 |
3 Replies
- DataInsights
Super User
Try this solution.
Create calculated columns:
Is Period End = IF ( FactTable[Period End] = EOMONTH ( FactTable[Period End], 0 ), 1 )Latest Run = VAR vLatestRun = CALCULATE ( MAX ( FactTable[Run Date] ), ALLEXCEPT ( FactTable, FactTable[Period End] ) ) VAR vResult = IF ( FactTable[Run Date] = vLatestRun, 1 ) RETURN vResultCreate measure:
Period End NAV = CALCULATE ( SUM ( FactTable[NAV] ), FactTable[Is Period End] = 1, FactTable[Latest Run] = 1 ) Hello, Thank you for the quick reply, When i enter the formulas above into Power BI Desktop, 5/31/2022 is the only NAV value that shows up. I need the NAV values for each month end.
- DataInsights
If you could share your pbix (remove confidential data) via a file service like OneDrive, I'll take a look.
