Hide partial YTD Column

[DieLem]

Hi!

See image attached. I have a Line and Column chart. The line is this year's Target and the colunms are the current year's Actual values. However it looks iffy with current year's values remaining the same with data yet to come.

Is it possible to have a function to display the line for the full 52 weeks but the column chart values only up until today?

Both the target and the actual use YTD measures and a Date Dimension table.

Thanks!

YTD Column Remove

[Ashish_Mathur · Accepted solution]

Hi,

Try this formula

IncrementTr YTD = if(VALUES('Date'[Date])<=TODAY(),CALCULATE([IncrementTr Total],DATESYTD('Date'[Date],"30/6")),BLANK())

[DieLem]

@anandav

See this link (File: TrackingExample3):

https://drive.google.com/drive/folders/1P10op_192ac_m-QGqt-QjolaLZSIMc_t

In the first tab (YTD Revenue [No Column Series]) I have used your formula and it works perfectly! In the second tab (YTD Revenue) I add the 'Revenue Stream' to the Column Series to show the revenue stream breakdown on the bar charts. This stops the whole graph with the lines from going to August and ends it at end of Jan 2018.

I want the graph to look like your example in the first tab with lines until August and bars until Jan but with the bars split when there is a parameter added to the Column Series to show breakdown.

Thanks!  

[anandav]

@DieLem

I am stumped. Not sure why this is happening.

May be @Ashish_Mathur can assist.

[Ashish_Mathur]

Hi @anandav,

I thought i have already answered @DieLem's question.  Have i missed something?  Is my solution giving any problems?

[anandav]

He has another problem when adding a column to stack chart. Check his latest question.

[Ashish_Mathur]

I'll let @DieLem clearly mention his problem.

[DieLem]

@Ashish_Mathur

You're solution works, however I can't use the Column Series to further change the way the colunms display. Once I add a criteria to the Column Series it cuts off the lines. Here an edited version of my problem to @anandav:

See this link (File: TrackingExample3):

https://drive.google.com/drive/folders/1P10op_192ac_m-QGqt-QjolaLZSIMc_t

In the first tab (YTD Revenue [No Column Series]) I have used your formula and another I have received, they both work perfectly! In the second tab (YTD Revenue) I add the 'Revenue Stream' to the Column Series to show the revenue stream breakdown on the bar charts. This stops the whole graph with the lines from going to August and ends it at end of Jan 2018.

I want the graph to look like your example in the first tab with lines until August and bars until Jan but with the bars split when there is a parameter added to the Column Series to show breakdown.

Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution here.

Hope this helps.

[DieLem]

Hey @Ashish_Mathur

Exactly what is required. Thank you!

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark it as Answer.

[Ashish_Mathur]

Hi,

Try this

=CALCULATE(SUM(Data[Sales]),DATESBETWEEN(Calendar[Date],DATE(YEAR(TODAY()),1,1),TODAY()))

Hope this helps.

[DieLem]

Hi @Ashish_Mathur

Unfortunately it doesn't work. Strong chance I am doing it wrong though! See the post above for a link to the example.

Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

Try this formula

IncrementTr YTD = if(VALUES('Date'[Date])<=TODAY(),CALCULATE([IncrementTr Total],DATESYTD('Date'[Date],"30/6")),BLANK())

[DieLem]

Hi @Ashish_Mathur

This is perfect thanks so much!

[Ashish_Mathur]

You are welcome.

[anandav]

@DieLem,

You could use an IF condition (either as a measure or new conditional column) to check whether there are values for that date and assign 0 as the value if it is blank.

Do you have a Date table and what is the relationship with the table that has the values?

Again more details of your data model and the sample PBIX file will help to understand your problem and help you.

[DieLem]

@anandav

Here the link:

https://drive.google.com/drive/folders/1P10op_192ac_m-QGqt-QjolaLZSIMc_t?usp=sharing (Filename: TrackingExample)

There is a date dimension table and two other tables. One (RegistrationTarget) contains target values for the rest of the year. Second (RegistrationTracking) contains the historical data which I would only like to see until today and 'blank out' the remainder of the year to come.

Thanks!

[anandav]

@DieLem

Glad you found the solution.

Another approach is to use cummulative total pattern:

IncrementTr YTD =
CALCULATE( sum(RegistrationTracking[Increment]), filter(All('Date'), 'Date'[Date] <= Max(RegistrationTracking[SnapshotDate])))

The advantage is you don't have to hard code the end date.

Check this article :

Calculating Cumulative Or Running Totals In Power BI using DAX

[DieLem]

Hey @anandav this also works, thanks!

I hear what you say about not hard coding, that's wise.

Both solutions work. However when I add a field to the column series to split graph into different revenue streams (PackageCode) it suddenly jumps the whole screen. 

Is there a reason for this?

[anandav]

@DieLem

An image and some ore details on what you are trying to do will be helpful.