Spread projectvalue across now and project_end_date

[Mister_PowerBI]

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?

[Anonymous · Accepted solution]

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