Date Measure

[NJ81858]

Hello, 

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:

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]

@NJ81858  Did it solve your challenge?

[Mikelytics]

Hi @NJ81858 

Please try the formula

DATESYTD(DimDate[Date])

DATESYTD function (DAX) - DAX | Microsoft Learn

Best regards

Michael

-----------------------------------------------------

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

@ me in replies or I'll lose your thread.

[Mikelytics]

Another option could be:

DATESBETWEEN=

var _lastdate = -> your code
var _firstdate = -> your code

RETURN

DATESBETWEEN(Dates[Date],var _firstdate,var _lastdate)

DATESBETWEEN function (DAX) - DAX | Microsoft Learn

Best regards

Michael

-----------------------------------------------------

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

@ me in replies or I'll lose your thread.

[NJ81858]

@Mikelytics 

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."

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]

Hi @NJ81858 ,

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?

In the end it would look like this (example)

DATESBETWEEN and Calculation =

DATESBETWEEN=

var _lastdate = -> your code
var _firstdate = -> your code

RETURN

CALCULATE(
   SUM(Table[Column]),
   DATESBETWEEN(Dates[Date],var _firstdate,var _lastdate)
)

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?

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. 🙂

Best regards

Michael

-----------------------------------------------------

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

@ me in replies or I'll lose your thread.

[NJ81858]

@Mikelytics This is my current data model:

I just want to apologize in advance for not being able to share my pbix file and masking all of the table names/fields, as there is highly sensitive data in the file. The highlighted table is obviously my date table, and all tables circled in blue report metrics that will use the custom date filter that this post is about. These metrics are more just reporting all values for the time period, instead of performing a calculation of sorts. The red circled tables need to be based on the same date table, but they only show a snapshot of the data from whatever the month selected is, hence why I want to have the user select only a month. In the end I hope that I can have the user select the month they want to see and the red circled tables will show only that month's data, and the blue circled tables will show all data from the beginning of the year to the end of the month selected.

[Mikelytics]

piuh , okay. But no worries I fully understand that you can not share the data, but at least a get a big picture. 🙂

I mean I know you want to show data but still you do a calculation right. For the red table you takr for example the sum or max of a column for a specific month. and for the blue tables you take the sum over several months. Something like this would be possible with the appraoch I described. 

Can you build an easy example with dummy data? I mean it can be a few rows manually typed in Excel.

- 1 date table (this I know how it looks like) 😉

- 1 red table example

- 1 blue table example

- and an example what you want to Calculate/vizualize

It only has to be a few rows per table

Best regards

Michael

[NJ81858]

@Mikelytics Ok I put together some test data as a proof of concept. This is a screenshot of what I want to get:

AS a note of reference, this table has two slicers that are only interacting with the visual that is in the same box, but in my actual project, I would like there to only be one slider like the one that selects a month name. As you can see, the "blue table" will show all metrics with dates between the beginning of the year and the end of the month name that is selected, while the "red table" will only show data for a singular selected month.

Unfortunately my organization does not allow for sharing of any files, as the file share is not an option for me to select.