How to draw a chart using today() in measure as X-axis

[neozhang]

hi, I have a table in Power bi has some columns, system number, system WT, and start date, finish date.

It has 2 calculated columns, using following formular. The idea I think is to make it can be filtered by Type, Size etc.

C3ProgressIndi-Plan = 'data'[C3 WT]*

 if([Today-C3/C4]>'data'[C3 Finish - Plan],1,

    if([Today-C3/C4]>'data'[C3 Start - Plan],

       DIVIDE([Today-C3/C4]-'data'[C3 Start - Plan],'data'[C3 Finish - Plan]-'data'[C3 Start - Plan],0),

    0)

 )

C3ProgressIndi-Current = 'data'[C3 WT]*

if([Today-C3/C4]>'data'[C3 Finish - Current],1,

  if([Today-C3/C4]> 'data'[C3 Start - Current],

            DIVIDE([Today-C3/C4]-'data'[C3 Start - Current],'data'[C3 Finish - Current]-'data'[C3 Start - Current],0),

 0)

)

 and [Today-C3/C4] = today(), which can update automaclly.

The main measure here is 

C3-Progress Current = DIVIDE(sum('data'[C3ProgressIndi-Current]),sum('data'[C3 WT]))

Now i would like to create a curve, using date of [Today-C3/C4] = today() s X-axis, and progress as Y-axis, but it doesnt work out.

I was trying to create a new table for those, but there are a few filter on System, such as type, area etc. which cannot be used if I create another table.

Can you pls suggest how I can do that? My guess is I will need to create another measure, followed the same formula but replaced Today by another calendar column, but it cannot be a simple copy paste work for the fomola, as I cannot indentify which WT should be choosen.

[VijayP]

@neozhang 

Curve means you want to show cumulative like a tragectory?

then use the Calculate(measure,Filter(all(DateTable),datetable[Date]<=max(Datetable[Date])) .

if not you can some pictorial representation in a note pad or somewhere to see what exactly you are looking for

[neozhang]

Thanks, VijaP, I updated the question, can you pls take another look? Thx

Also, not sure if it is possible I can upload the sample data, via pbi format, I tried earlier, got the note that does not support this format.

[CoreyP]

When you say "curve" do you mean you want to plot the function of a line with exponential growth? For example, you know what the Y value is on day 1, and what the Y value is on day 30, but the line from Y1 to Y30 needs to be an exponential curve and not linear?

[neozhang]

hi Corey, not exponetial type, just simple line chart as you mentioned. 
Any suggest how to fix this? Many Thx

[CoreyP]

I'm not entirely sure I understand, but let me throw out an example, and see if you can apply it to what you need. 

Let's say you know what the value is for TODAY(). ( Let's pretend TODAY() is 12/21/2023 )

The start date for when the value started accruing was the beginning of the month, 12/01/2023. 

You assume that the value as of TODAY() went up with linear growth, the same amount every day. For example, if today's value is 210, it went up by 10 each day, beginning on the first. ( 210 / 21 days = 10 )

In your measure, you can adjust the Y value by multiplying it by which day is on the X axis, by using DAY( [Date] ). 

Does this make any sense? lol

[neozhang]

Thanks for the reply, but I dont think we are on the same page.

The current setup can calculate a Progres value via the measure [C3-Progress Current], everyday as the other measure [Today-C3/C4]is changing itself everyday.

The trickly part is the 2 calculated columns, which are both used in the current measurement, so I cannot use them directly to the new measurement.

what I want is a line chart, and X-axis is date, and used as [Today-C3/C4]  in the measurement [C3-Progress Current], and use [C3-Progress Current] as Y-axis.

For example, for today 12/21, I can calculate a total (without any filter) [C3-Progress Current] value as 0.45, and if I change this measure [Today-C3/C4], to 12/30, the total (without any filter) [C3-Progress Current] value now is 0.52, and I want (12/21, 0.45), (12/30 0.52) in the line chart.

another concern to me is I would like to keep the filter applicable, such as Type, Size etc. 

Thanks in advance.

[CoreyP]

Yeah, you've completely lost me I'm afraid. Can you maybe try drawing a picture of what you want you're output to be?

Your measures are also confusing to me... I'm not even sure what it is you're calculating. Please try to better explain your data and what you want to calculate.

I see 4 dates in your dataset -- Two sets of Start Dates and Finish Dates. One set is "Current" and the other is "Planned." All of the dates appear to be in the future. What is the different between "Current" and "Planned" dates?

Additionally, you keep saying that the measure [Today-C3/C4] is just equal to today's date, by using the TODAY() function, correct? 

Are you wanting to know how much of a project is completed "as of today" based on the Start and Finish date? For example, Thing A has a Start date of 12/18/2023, and a Finish date of 12/22/2023. Thing A has a total of 5 days between the start and finish days. If we're looking at the report today, and today is 12/21/2023, Thing A is 80% complete? If we check the report tomorrow, or any day thereafter, it'll be 100% complete?

[neozhang]

Sorry about the complexity, it is a team work, and I was trying to simplfy to the part which bugs me.

You are correct on the progress calculation idea. The main point for this table is to calculate the progress among systems, with applicable filter. The columns in left side are mostly used as the filter I just mentioned.

For the Progress calculation, each system will have its own weight, as in column C3 WT, we will need to use the weighted average when using filteres. as for individual system, the actual progress is as you mentioned, the duration from today to start date, devided by totoal duration from the completiond date to start date. We added 2 IF function to eliminate the cases that today date is before start date or after completion date. 

What I would like to do now is using the date in X-axis in the calculation, and draw a line for progress in Y-axis, but still with similar filter working. 

Hope this can help explain my case. Thx