cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
D1ltang Frequent Visitor
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?

 

Thanks in advance

 

Dilshan 

6 REPLIES 6
Abduvali Established Member
Established Member

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=
ADDCOLUMNS (
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

D1ltang Frequent Visitor
Frequent Visitor

Re: calculate dates between two measures

Hi @Abduvali

 

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?

 

pbiforum.pngThe final Visual is what I am trying to achieve

 

Thanks again,

 

Dil

Abduvali Established Member
Established Member

Re: calculate dates between two measures

@D1ltang

 

 

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

 

Thanks

D1ltang Frequent Visitor
Frequent Visitor

Re: calculate dates between two measures

pbiforum.png

 

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

 

Thanks,

 

Dil

v-yulgu-msft Super Contributor
Super Contributor

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.
Abduvali Established Member
Established Member

Re: calculate dates between two measures

Hi @D1ltang,

 

 

See for the solution in my Power BI library:

 

Hope this helps.

 

 

Regards

Abduvali

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 317 members 3,327 guests
Please welcome our newest community members: