Add MTD goal line that increases at the same value each day on my graph

[carlacervonif]

I am currently creating a combo line and stacked column chart for outputs. My data has 4 columns: transaction date, quantity, selling price and total value (quantity*selling price). X axis is days of the current month and y-axis columns are total value for each day. I have two lines, MTD and daily goal (constant line at y = $100,000). I want to add another line that shows what the goal MTD would be if we would fulfill our daily goal at a rate of $100,000 per day. For example, day 1: $100,000, day 2: $200,000, day 3: $300,000, etc., all the way to the last day of the month which would total $2 million (in working days). An important thing to mention is that there are mutiple transactions for the same day, so I can't just add a column with those values to my data. Also, I don't have a date table but the transaction date column has a PowerBI-generated date hierarchy.  Any ideas or suggestions? Anything is appreciated! Thank you

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.  Also, in accumulating the daily goal, do you want to exclude both Saturday and Sunday?

[carlacervonif]

Good morning! Yes I would like to exclude Saturday and Sunday, here is the download link to some sample data: https://app.powerbi.com/links/UDPiJGt10P?ctid=5dbe144b-33d6-4f65-8c82-befe5f2d5b6c&pbi_source=linkSh...

[Ashish_Mathur]

That takes me to a sign-in page

[carlacervonif]

I aplogize, I'm having a hard time figuring out how to send the file without asking for a sign-in. I will attach a screenshot of my screen with columns and measures.

I'll also attach a screenshot of the spreadsheet so you can get a better idea of what I meant about having mutiple transaction values with the same date. The data goes up to the day before and is updated everyday! 

Let me know if this works, but if not, thanks anyways for your time and patience!

[Ashish_Mathur]

Hi,

Upload the file to Google Drive and share the download link here.

[carlacervonif]

Hi, I uploaded the PBI and spreadsheet filed to this folder in Google Drive: https://drive.google.com/drive/folders/1yPRTt0x7vNr6sGKjAY1PtEdgnyHUZLww?usp=sharing

Thanks again for the help!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[amitchandak]

@carlacervonif , Assuming you are using date table, Try a measure like

Calculate(

Sumx(Summarize(Table, Date[Date], "_1", 100000), [_1]), datesmtd(Date[Date]) )