cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## calculate dates between two measures

Hi I have 2 simple measure. One takes the selections on the slicers into account and creates a date. The other uses this newly created date to work out the 1st of the month a year ago. These both work and behave as expected.

```SelectedDate =
DATE(
IF(ISFILTERED('Working Date'[CalendarYear]),SELECTEDVALUE('Working Date'[CalendarYear]),YEAR((max('Site Ticket Metrics'[TicketTimeStamp])))),
IF(ISFILTERED('Working Date'[MonthName]),CALCULATE(MAX('Working Date'[MonthOfYear]),FILTER('Working Date','Working Date'[MonthName] = SELECTEDVALUE('Working Date'[MonthName]))),MONTH(max('Site Ticket Metrics'[TicketTimeStamp]))),
MAX('Working Date'[DayOfMonth])) ```
`SelectedDateMinus12 = DATE(YEAR(EDATE([SelectedDate],-11)),MONTH(EDATE([SelectedDate],-11)),MIN('Working Date'[DayOfMonth]))`

What I would like to do is create a list of dates between these two measures.

I have tried the following but it just returns a single blank in a table visual:

`Dates between  SelectedDates = CALCULATETABLE(values('Working Date'[FullDate]),FILTER(ALL('Working Date'[FullDate]),MAX('Calendar Date'[FullDate])<= [SelectedDate] && MAX('Working Date'[FullDate])>=[SelectedDateMinus12]))`

Does anyone have any Ideas as to how I can make this work?

Dilshan

6 REPLIES 6
Highlighted
Skilled Sharer

## Re: calculate dates between two measures

Hi @D1ltang,

Don't know if you tried the following if not give it a try this might do a trick for you:

Create new Calendar table and create an active  Many-to-One relationship on dates with your Working table. then use your new Date from Calendar table in your table visual.

DAX for Calendar table:

Calendar=
CALENDAR (DATE(2016,1,1), DATE(2017,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)

Hope this helps.

Regards

Abduvali

Frequent Visitor

## Re: calculate dates between two measures

Thank you for taking the time to respond.

Unfortunately the start and end dates that I am using are not static. They are generated as a result of the users input via slicer selection. This solution would be perfect if I could pass my two measures as the Start and End Date in your calendar calculation.

Have you got any other suggestions?

The final Visual is what I am trying to achieve

Thanks again,

Dil

Highlighted
Skilled Sharer

## Re: calculate dates between two measures

@D1ltang

Can you make a better and bigger screenshot 🙂 cant see anything on this one and please include a screenshot of your table with a Date column.

Thanks

Highlighted
Frequent Visitor

## Re: calculate dates between two measures

Hopefully that is clearer and has what you would like to see.

Thanks,

Dil

Highlighted
Microsoft

## Re: calculate dates between two measures

Hi @D1ltang,

You could try to create a calendar table, then, filter this calendar table with the two measures [SelectedDate] and [SelectedDateMinus12] to get the list of dates between specific date range.

For more advice, please provide sample data of source table so that we can test.

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Skilled Sharer

## Re: calculate dates between two measures

Hi @D1ltang,

See for the solution in my Power BI library:

Hope this helps.

Regards

Abduvali

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors