Calculate Date and Weeks Funds Run Out

[pwrbipv]

Hi,

I am using three different "average end of year projection cost" fields that average the incurred cost of the most recent 2, 4, and 8 weeks.

I am also using an "average end of year projection cost" field that averages the cost over the total weeks of the year.

Questions:

1) How can I use each of the four weekly cost averages to determine when the remaining cash will be gone? 

2-week: 22.8 weeks of spending left

2) How can I show the week the cash is gone?  "Cash is spent the week of Jul 11-15, 2022"

Example, year to date costs total $30,000

Year to date total costs: $10,000

Remaining cash: $20,000

2 week weekly cost average:  $875-this equals to 22.8 weeks remaining: $20,000/$875 so cash is gone the week of Jul 11-15, 2022

4 week weekly cost average: $800-this equals to 25 weeks remaining: $20,000/$800 so cash is gone the week of Aug 1-5, 2022

8 week weekly cost average: $580-this equals to 34.5 weeks remaining: $20,000/$580 so cash is gone the week of Oct 10-14, 2022

Year to date weekly cost average: $550-this equals to 36 weeks remaining: $20,000/$550 so cash is gone the week of Oct 17-21, 22

Thank you.

[pwrbipv]

Hi,

I was unable to get your solution to work. But, I am close to getting what I need. My question now is "How can I use a  hidden field in a new measure?"

I was able to compute the number of weeks remaining.  As an example, I know there are 7.33 weeks left as of this past Friday, 2/18/22.  I need to show in a visual the date 4/8/22 because that is the 7th week from this past Friday, 2/18/22.  

The issue is using the "as of Friday (most recent)" date.  There is a field in the home table "TIME" called AS_OF_FRIDAY_DATE   but it is hidden.  I can use it in a visualization but cannot seem to use it in a new measure.  

I beleive this formula will give me what I want.  TIME'[AS_OF_FRIDAY_DATE]*([WEEKS REMAINING]*7)

How can I use this hidden field in a new measure?

[amitchandak]

@pwrbipv ,

Assume you are able to create week remaining

then you cam find week like

measure  =

var  _max=maxx('Date'[Rank]) + [week remaining]

return

Maxx(filter(all('Date') , 'Date'[Rank]) =_max) , 'Date'[Week Name])

For week rank refer

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8