Forum Discussion

jatneerjat's avatar
jatneerjat
Icon for Helper V rankHelper V
8 years ago

Get data for all dates between StartDate and EndDate in DAX

Hi,

 

I have a table tblResourceInfo in which it have columns like resourceName,Country,PeriodStartDate and PeriodEndDate and I created a calendar table tblCalendarDates by using CalendarAuto().Now i want to see number of resources active on a particular month(Year) and also for a particular country so how can i create a relationship between the dates of tblCalendarDates and dates of tblResourceInfo or can i add some calculated date column in tblResourceInfo so that i can easily filter data.

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    8 years ago

    Hi jatneerjat,

     

    You could create the relationship by the column PeriodStartDate or PeriodEndDate with the Date column in the Calendar table like below. 

     

    Then when you create the slicer with the Date column in Calendar table, you could filter the resources name.

     

    For another option, you could create a calculated column Year with the Year Function in table tblResourceInfo .

     

     

    Then you could create the Slicer with Year and filter the resource name.

     

    Note: If you try with the second way, you don't need to create a Calendar table and create relationship.

     

    Best Regards,

    Cherry

    • jatneerjat's avatar
      jatneerjat
      Icon for Helper V rankHelper V
      8 years ago

      Hi v-piga-msft ,

       

      Thanks for your response but you have used only startdate.so it will give me the resourcename who have startdate in table.I also want the rsource who are active between startdate and enddate.

      For eg. if a resource has startdate is 2016/04/05 and enddate is 2018/09/20 so now if i select year 2017 then this resource should be counted as active.

       

      Thanks,

      Neeraj