Show Prior dates of data on chart when single date is selected in Slicer

Power BI
1

Hi,

i have 2 tables…

-one is dimension table (‘Table1’ with Date column)

-second is fact table (‘scurve with cummulative’ with Date column)

-fact and dimension tables are connected via date columns

-my slicer has date column from table 1

-my area chart has x axis & y-axis (date column & cummulative cost- both from ‘scurve with cummulative’ table)

  • if i select a month (eg. september) from slicer…i want the graph to show values of semtember and all values before that month. Please help

4123
4123735×191 13.1 KB

image

2

The issue is the way you have the slicer setup, it will filter by the selected date, if you change the slicer type to date and choose before it should then filter to dates before the selected date (but wont have selections).

The other option is to have a new disconnected table with your date selections in, and use that as the slicer, in your cumulative measure add a VAR to store the selected value in the slicer, and amend your cumulative measure by either using an if statement if(SlicerDate > Max(Date[Date]), [Cumulative]) or change the working of the cumulative to be <= date[date] && <= SlicerDate or what ever method brings back a blank value if the date[Date] is greater than the VAR SlicerDate

Edited this, it would make more sense to use the if statement approach that way it wouldnt show a value the calculate approach would show a flat line as it wouldnt return a blank.

1 Like
3

Hi, thanks for helping me out @kylie.oconnell .
Unforutunately i’m still unable to figure it out the correct measure. I’ve attached the pbi file. Would be really grateful if you could please help me out further.

powerbi dashb.pbix (421.8 KB)
powerbi dashb.xlsx (33.8 KB)

4

powerbi dashb.pbix (384.4 KB)

I dont know what is happening with the relationship with the date table and the fact table, I usually do all the modelling in power query, so I used the date from the scurve fact table, really you should build a calendar table that connects to all your tables with dates.

So I wouldn’t use the model in the file I have attached it is just to demonstrate you need a disconnected table with dates for the slicer, a measure for cost, and a measure with an if statement to check if the date is less than the selected date in the slicer otherwise returning blank.

1 Like
5

thanks a lot