To create a measure to select values in dates for Months and Years formatting it befor the dashboard

[Anonymous]

Hi,

I would like to use a measure, to access directquery date data with begin and end dates

To get the desired result I was considering using 'SELECTEDVALUE' to select the relevent part of the date [the 'MM' or 'YYYY'].

'When' the date contains the month value in the 4th and 5th character (date format is DD/MM/YYYY), for instance when it contains 01, it could select all 'January' options, and the same for the next 11 months consecutivly.

The tricky part comes when trying to include the events that happen beginning like 31st of month 01 and ending 01st of month 02. For this I need to prioritize the End date, as that's when the event has concluded. So in above case the event ended on 01st of month 02 so needs to be filtered to be included in the Febuary selection.

The selected file I'm building the measure for month into is Maand.csv, so in Model view it shows up as Maand.

Filtering on year works the same from the 7th to the 10th character, starting from 2015. If the event passes over into the new year it's included in the new year, and is using Jaar.csv, so shows up as Jaar.

Can you help with a suggestion?

[Anonymous]

For those thinking about including Ibendlin's suggestion.

A Calendar table looks something like this:

Calendar = CALENDAR(DATE(2015,1,1), DATE(2023,12,31))

The question probably requires the DAX use of MONTH and YEAR, how you go about building up your DAX though to answer my question I've left up to the person answering the question. What I've requested in the question is that you try and use SELECTEDVALUE. Up to the answering party ofcourse. Since I couldn't get it to work properly.

[lbendlin]

I would suggest you use a proper calendar table that includes all dates, month numbers and names etc.  Eliminate all your other tables.