Forum Discussion
Hide partial YTD Column
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
Hi,
Try this formula
IncrementTr YTD = if(VALUES('Date'[Date])<=TODAY(),CALCULATE([IncrementTr Total],DATESYTD('Date'[Date],"30/6")),BLANK())
19 Replies
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.
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!
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
Hi,
Try this
=CALCULATE(SUM(Data[Sales]),DATESBETWEEN(Calendar[Date],DATE(YEAR(TODAY()),1,1),TODAY()))
Hope this helps.
Unfortunately it doesn't work. Strong chance I am doing it wrong though! See the post above for a link to the example.
Thanks!Hi,
Try this formula
IncrementTr YTD = if(VALUES('Date'[Date])<=TODAY(),CALCULATE([IncrementTr Total],DATESYTD('Date'[Date],"30/6")),BLANK())
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!
