Spread projectvalue across now and project_end_date

Mister_PowerBI — Wed, 21 Dec 2022 07:05:44 GMT

Hello everybody,

I have a hard time getting this figured out and was hoping maybe somebody can help me out .

Let's say I have a database with projects that have an end_date and a remaining_project_value column. I would like to spread the remeaning_project_value over the dates between UTCNOW() and end_date and show this in an interactive visual that can be switched. So the visual needs to be able to switch from week to month, and show the remaining_project_value accordingly.

Can anybody put me in the right direction?

Re: Spread projectvalue across now and project_end_date

Anonymous — Thu, 22 Dec 2022 01:51:06 GMT

Hi Mister_PowerBI ,

I created some data:

Here are the steps you can follow：

1. Create calculated column.

Week = WEEKNUM('Table'[end_date ],2)

2. Enter data – create a table.

3. Create measure.

Flag = var _select=SELECTEDVALUE('Slicer_Table'[Slicer]) var _today=TODAY() var _minweekdate= MINX(FILTER(ALL('Table'),YEAR('Table'[end_date ])=YEAR(MAX('Table'[end_date ]))&&'Table'[Week]= MINX(FILTER(ALL('Table'),'Table'[end_date ]=_today),[Week])),[end_date ]) return SWITCH( TRUE(), MAX('Slicer_Table'[Slicer])="Month"&&MAX('Table'[end_date ])>=DATE(YEAR(_today),MONTH(_today),1)&&MAX('Table'[end_date ])<=_today,1, MAX('Slicer_Table'[Slicer])="Week"&&MAX('Table'[end_date ])>=_minweekdate&&MAX('Table'[end_date ])<=_today,1, 0)

4. Place [Flag]in Filters, set is=1, apply filter.

5. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

topic Re: Spread projectvalue across now and project_end_date in DAX Commands and Tips

Spread projectvalue across now and project_end_date

Re: Spread projectvalue across now and project_end_date