- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
The final Visual is what I am trying to achieve
Thanks again,
Dil
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hopefully that is clearer and has what you would like to see.
Thanks,
Dil
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
08-20-2024 09:13 AM | |||
09-27-2024 11:42 AM | |||
06-25-2024 03:02 PM | |||
07-24-2024 12:31 AM | |||
09-23-2024 08:19 AM |