Date Measure

Question

Hello,&nbsp;&nbsp;I am trying to put together a measure that will give me a date range from the beginning of the year to the end of the user selected month. For example if a user selects July, I want the data from January 1 through July 31. This is what I have so far for my measure that will assign each day in that dynamic range with a 1 so that I can place a filter on all of my visuals to only show data that has a 1 as the value:&nbsp;The issue with it in its current state is that no matter what month the user selects, it displays a 1 for every date. I just am not sure where my logic is going wrong in the measure, so any help would be appreciated!

mikelytics · Answer

Another option could be:&nbsp;DATESBETWEEN=

var _lastdate = -&gt; your code
var _firstdate = -&gt; your code

RETURN

DATESBETWEEN(Dates[Date],var _firstdate,var _lastdate)&nbsp;DATESBETWEEN function (DAX) - DAX | Microsoft Learn&nbsp;Best regardsMichael-----------------------------------------------------If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly. Appreciate your thumbs up!@ me in replies or I'll lose your thread.

mikelytics · Answer

Hi&nbsp;NJ81858&nbsp;&nbsp;Please try the formula&nbsp;DATESYTD(DimDate[Date])&nbsp;DATESYTD function (DAX) - DAX | Microsoft Learn&nbsp;&nbsp;&nbsp;Best regardsMichael-----------------------------------------------------If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly. Appreciate your thumbs up!@ me in replies or I'll lose your thread.

mikelytics · Answer

NJ81858&nbsp; Did it solve your challenge?

nj81858 · Answer

Mikelytics&nbsp;&nbsp;When I try the DATESYTD function and put it in a table, it gives me the error that says "Calculation error in measure: A table of multiple values was supplied where a single value was expected."&nbsp;When I try the DATESBETWEEN function, it only returns the first value in the date table, that being, 1/1/2022, no matter what month I choose as my 'Selected Month' that the end date is supposed to be based on.

mikelytics · Answer

Hi&nbsp;NJ81858&nbsp;,Yes, because you have to use it in combination with a calculation function. Because also with your current measure you want to calculate something else in the end, right?&nbsp;In the end it would look like this (example)&nbsp;DATESBETWEEN and Calculation =

DATESBETWEEN=

var _lastdate = -&gt; your code
var _firstdate = -&gt; your code

RETURN

CALCULATE(
   SUM(Table[Column]),
   DATESBETWEEN(Dates[Date],var _firstdate,var _lastdate)
)&nbsp;If this dopes not help you then it would be helpful to get the full picture of want you intend to achive. You want a list of YTD dates but this is not the end right?&nbsp;I assume you want to do a YTD calculation with a specific value. SO having a picture of the data model or some more contect would be good. 🙂&nbsp;Best regardsMichael-----------------------------------------------------If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly. Appreciate your thumbs up!@ me in replies or I'll lose your thread.

Forum Discussion

Date Measure

8 Replies

Related content

Date measure minus one year

Measure expire date

Measure

Ignoring Date Slicer with measure

Remove date filter from DAX measure