Forum Discussion

D1ltang's avatar
D1ltang
Frequent Visitor
8 years ago

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 

Need Help

6 Replies

  • Abduvali's avatar
    Abduvali
    Skilled Sharer
    8 years ago

    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's avatar
      D1ltang
      Frequent Visitor
      8 years ago

      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

      • Abduvali's avatar
        Abduvali
        Skilled Sharer
        8 years ago

        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

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    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