topic Re: Create Measure Indicator to show "1" for all dates from start of the year till the selected date in DAX Commands and Tips

Create Measure Indicator to show "1" for all dates from start of the year till the selected date

Anonymous — Sun, 01 Mar 2020 21:56:17 GMT

Hello,

I want to create a measure that should give me an indicator "1" from the start of the year until the selected date.

For example, in the date filter if I selected 2/1/2020 then I should get an indicator "1" for 1/1/2020 and 2/1/2020 and "0" for all the other dates.

Below is the sample screenshot

Re: Create Measure Indicator to show "1" for all dates from start of the year till the selected date

Greg_Deckler — Sun, 01 Mar 2020 22:00:54 GMT

So, perhaps something like:

Indicator = VAR __SelectedDate = SELECTEDVALUE('Date Filter'[Date]) VAR __Date = MAX('Table'[Date]) VAR __StartOfYear = DATE(YEAR(__SelectedDate),1,1) VAR __EndOfYear = DATE(YEAR(__SelectedDate),12,31) RETURN IF(__Date >= __StartOfYear && __Date <= __EndOfYear,1,0)

If you don't care about the end of the year, just get rid of that filter.

Re: Create Measure Indicator to show "1" for all dates from start of the year till the selected date

Anonymous — Sun, 01 Mar 2020 22:15:52 GMT

Thanks.....I tried this but it is giving me 1 for all the date from 2017 to 2020.

I am using between for dates in slicer as shown below in the picture.

Re: Create Measure Indicator to show "1" for all dates from start of the year till the selected date

Greg_Deckler — Sun, 01 Mar 2020 22:19:46 GMT

OK, yeah in that circumstance, SELECTEDVALUE will not work, try:

Indicator = VAR __SelectedDate = MAX('Date Filter'[Date]) VAR __Date = MAX('Table'[Date]) VAR __StartOfYear = DATE(YEAR(__SelectedDate),1,1) VAR __EndOfYear = DATE(YEAR(__SelectedDate),12,31) RETURN IF(__Date >= __StartOfYear && __Date <= __EndOfYear,1,0)

This is why details matter! 🙂

Re: Create Measure Indicator to show "1" for all dates from start of the year till the selected date

Anonymous — Sun, 01 Mar 2020 22:31:13 GMT

For some reason, this is still not working. I am using this Dax and I removed the EndOfYear Dax and still gives me 1 for all the date range from 2017 to 2020

TEST =

VAR __SelectedDate = MAX('ESL_Date_Dim'[DateValue])

VAR __Date = MAX(ESL_Date_Dim[DateValue])

VAR __StartOfYear = DATE(YEAR(__SelectedDate),1,1)

RETURN

IF(__Date >= __StartOfYear,1,0)

Re: Create Measure Indicator to show "1" for all dates from start of the year till the selected date

Greg_Deckler — Sun, 01 Mar 2020 22:36:08 GMT

Not sure, see attached PBIX where it seems to function fine.

Re: Create Measure Indicator to show "1" for all dates from start of the year till the selected date

Anonymous — Sun, 01 Mar 2020 23:12:07 GMT

ohhh!!!!! This pbix has 2 tables one date dim and another table and your dax is using one date from date dim and one from another table.

"VAR __SelectedDate = MAX('Calendar'[Date])

VAR __Date = MAX('Table'[Date])"

In my case, my pbix has 5 facts that are connected to datevalue from date dim.

How to use all 5 facts date in "VAR __Date = MAX('Table'[Date])"?

Re: Create Measure Indicator to show "1" for all dates from start of the year till the selected date

Greg_Deckler — Sun, 01 Mar 2020 23:16:38 GMT

OK, we probably need to back up here. My first question would be, if all of your tables are connected via a single Date filter and you are slicing on that date filter, how is it that you have a table visualization that would show all of your dates in the first place? Are you using Edit Interactions? Second, I'd need to see your model with how all the tables are connected together. Third, I probably need some more clarity around exactly what you are trying to achieve here and what columns and measures will be in the visual that you are trying to create.

Re: Create Measure Indicator to show "1" for all dates from start of the year till the selected date

Anonymous — Mon, 02 Mar 2020 00:13:45 GMT

My first question would be, if all of your tables are connected via a single Date filter and you are slicing on that date filter, how is it that you have a table visualization that would show all of your dates in the first place?

All my facts are connected to date dim by date and I am using "Date" from my date dimension in my slicer.

Second, I'd need to see your model with how all the tables are connected together.

Yes, All my facts are connected to the Date dimension. We are doing this modeling in the Time Xtender tool by creating a Semantic layer and publishing it to SSAS. (Instead of creating SSAS models in visual studio we are using Time Xtender tool for creating models)

Third, I probably need some more clarity around exactly what you are trying to achieve here and what columns and measures will be in the visual that you are trying to create.

I am sorry......I should have provided more information in the beginning.

Re: Create Measure Indicator to show "1" for all dates from start of the year till the selected date

Greg_Deckler — Mon, 02 Mar 2020 01:15:10 GMT

OK Anonymous here are the issues that I see with this approach. You have a Date dimension that is connected to all of your tables. You can select a range of these and they will presumably show up in a table visualization. So far so good.

Now the question is, what Date are you displaying in your table visualization? Because if it is the Date from the Dimension table, there is a problem. Once you introduce your measure into that visualization. If you use SELECTEDVALUE or MAX to retrieve the Date, it will retrieve the date within the context of your table visualization, as in the date in the current row. This is not what you want, you want the date that is the maximum date within the slicer. So, what I believe will fix your problem in this case is to use ALLSELECTED:

Indicator = VAR __SelectedDate = MAXX(ALLSELECTED('Calendar'[Date]),[Date]) VAR __Date = MAX('Table'[Date]) VAR __StartOfYear = DATE(YEAR(__SelectedDate),1,1) VAR __EndOfYear = DATE(YEAR(__SelectedDate),12,31) RETURN IF(__Date >= __StartOfYear && __Date <= __EndOfYear,1,0)

However, if this is not the case there is likely a different solution. I updated the PBIX and attached it to be more representative of your model. Now there is a relationship between the tables.

Re: Create Measure Indicator to show "1" for all dates from start of the year till the selected date

Anonymous — Mon, 02 Mar 2020 16:51:11 GMT

Thanks. This worked for me. If I have to do it for Quarter, how to do that?