Using a date slicer for data with a from and to range

[Anonymous]

Hi, I am new to PowerBI so forgive me if this has already been asked, but for all the searching I have done, I can't quite grasp my requirement.

I have a dataset that has a number of date fields that drive the data.

CompletionDate	URN	Name	ValidFrom	ValidTo
2021/10/01	1	Robert	2020/01/01	2021/08/08
2021/10/01	1	Bob	2021/08/09	2021/09/07
2021/10/01	2	Frank	2020/01/01	2021/09/07

When a user supplies the date of 2021/09/07 the display will show URN 1, Bob and URN 2, Frank. However if the date supplied is 2021/08/08 the display wil show URN 1, Robert and URN 2, Frank.

There is also another stipulation required that the data required is for 1 month after the date given and for a duration of months. 

So, this is how I have gone about solving this issue.

I have a calendar dataset, which includes the date and using this date as a reference create column that are the start and end dates of the duration required. For example Any date in september will have the start date as the 1st of October and end date of 30th November. I wish tho make this dynamic, but I will try that later once I have this initial problem sorted.

I have create a new table that is referenced from this date table:

CTF_Dates = 
var __now = NOW()
var __year = IF(MONTH(__now) < 3, YEAR(__now) - 1, YEAR(__now))
var __month = IF(MONTH(__now) < 3, MONTH(__now) + 9, MONTH(__now) - 3)
var __day = DAY(__now)
RETURN
    DATESINPERIOD
    (
        'dim DateDimension'[Date],
        DATE(__year,__month,__day),
        6,
        MONTH
    )

This gives me a 6 months calendar for which the date can be selected.

So I have then created a measure for the selectedDate:

CTF_SelectedDate = 
    IF( 
        SELECTEDVALUE(CTF_Dates[CTF_Date]) = 0,
        TODAY(),
        SELECTEDVALUE(CTF_Dates[CTF_Date])
    ) 

This will give me today if no date is selected, and appears to work.
The ther dimensions I require are the to and from dates to display the data from.

CTF_FirstDayOfDisplayMonthKey = LOOKUPVALUE('dim DateDimension'[FirstDayOfNextMonthKey],'dim DateDimension'[Date],CTF_Dates[CTF_SelectedDate])

CTF_LastDayOfDisplayMonthKey = LOOKUPVALUE('dim DateDimension'[LastDayOfMonthTwoAfterKey],'dim DateDimension'[Date],CTF_Dates[CTF_SelectedDate])

DisplaySelectedRecord = 
var SelectDateKey = CTF_Dates[CTF_SelectedDateKey]
RETURN
if (
    VALUE('dataTable'[MinImportDate]) <= VALUE(SelectDateKey) &&
    VALUE('dataTable'[MaxImportDate]) >= VALUE(SelectDateKey)
   ,"Y","N"
)

 I am then using this as a column that I can use with a filter to only display the 'Y'.

So that is the method that I chose to use. Whether it is the best practice, well that is why I am here to be instructed by people who now better than I, and I know nothing so am ready to learn.

I have set up a simple desktop to test and this is my finding.

So you can see when I change the date the results are correct in the top 3 cards. But on the last card the date is always the same?

How do I get the SELECTEDVALUE to work with the data table? I have no relationships between the data table and the date table.

This is just a simple WHERE clause is SQL but I just can't figure out how to do the same in PowerBI. Any help will be greatly appreciated. 

[lbendlin]

CTF_SelectedDate = 
    IF( 
        SELECTEDVALUE(CTF_Dates[CTF_Date]) = 0,
        TODAY(),
        SELECTEDVALUE(CTF_Dates[CTF_Date])
    ) 

Under which conditions would CTF_Date be equal to 0 ? 

Can you please post the code for the "First DisplayRecord" measure?

[Anonymous]

Hi Ibendlin,

Thank you for looking at my post.

I read somewhere that if the value was in the Blank state, no date was selected then that was equal to 0. Which seems to hold true in the test.

First Display Date is a measure I created to figure out why the displaying data wasnt moving when I changed the date. So it's the same as

SelectDateKey = CTF_Dates[CTF_SelectedDateKey]

So, looking into what you have said. I created the 3 measure that I wanted. And... perfect

 

 So I have changed DisplaySelectedRecord to a table and used the new measures.

CTF_Id_Within_Plan =

FILTER (

'dataTable',

VALUE('dataTable'[MinImportDate]) <= VALUE([mSelectedDate]) &&

VALUE('dataTable'[MaxImportDate]) >= VALUE([mSelectedDate]) &&

VALUE('dataTable'[CompletionDate]) >= VALUE([mDisplayFromKey]) &&

VALUE('dataTable'[CompletionDate]) <= VALUE([mDisplayToKey])

)

But the result in the data it is still the same. The result is for todays data, and not for the selected date.