Backlogging a Date

[NJ81858]

Hello,

I am trying to create a measure that will give me every date between the beginning of the year and the selected month, with the intent that the user will only select a certain month and will get data from the beginning of the year to the selected month instead of complicating things by selecting a specific day. I already have a measure for the selected month and it is being used in what I currently have. This is what I currently have:

and this is the result that currently shows no matter what the selected month is:

I just am not sure where my logic is going wrong that it only gives a 1 to the first day of each month instead of every day within the given range. Thank you in advance!

[NJ81858]

Update:

I have done some research that others have had success using and my dax code is now: 

IF('Dates'[Date] >= _firstdate && 'Dates'[Date] < _lastdate, 1, 0)

and this gives me the error:

"A single value for column 'Date' in table 'Dates' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

[v-yinliw-msft]

Hi @NJ81858 ,

You can try this method:

New a column called Month:

Month = MONTH('Date'[Date])

Then create the measure:

Select_Month = SELECTEDVALUE('Date'[Month])

Backlog Range = IF(SELECTEDVALUE('Table'[Month]) <= [Select_Month], 1, 0)

The result is:

The slicer should not in the same table with the Measure.

Hope this helps you. Here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NJ81858]

@v-yinliw-msft Ok I am not sure what is going on with my file, but I have everything copied and pasted from your file and it is showing all values as 0 currently. The only differences that I have in my attempt are that my table is called 'Dates' instead of 'Date' and my measure is called 'Backlog Range Test' instead of 'Backlog Range'. I have created a new pbix file and attempted your solution on my own and it worked in my new file, but not in my file that I am currently working on. Can you see anything wrong with my attempt at your solution?

[v-yinliw-msft]

Hi @NJ81858 ,

There are something need to check: Please use the column in different table to be a slicer.

And please check the tables have no relationship, if they have, probably you need to create a new data table to be a slicer.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NJ81858]

This is what I have, and the two date tables don't have any relationships between them. The 'Dates' table does have relationships with other tables in my model, because that is my central date table that I am using across my report.