Forum Discussion

tobiasmcbride's avatar
tobiasmcbride
Icon for Helper III rankHelper III
6 years ago

Number at a particular date

Hi,   I have the following data on headcount.   Name Position Start Date End Date Current Status Joe Bloggsd Head of Marketing 21/02/2017   Established Noah Anyone Head of Opera...
Need Help
Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
6 years ago

Hi tobiasmcbride 

 

You can add an extra table to your model that expand dates ( start to end ).

On the attached, you will find a file that applies this concept to your sample.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn


 

  • tobiasmcbride's avatar
    tobiasmcbride
    Icon for Helper III rankHelper III
    6 years ago

    How do you expand those dates to get that as cannot see a formula anywhere in DAX for epandDates?

    • Mariusz's avatar
      Mariusz
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Hi tobiasmcbride 

       

      In Query Editor you will see a function epandDates should really be expandDates (typo), this function is invoked in emplyeeDates table, please see the video.

       

      Best Regards,
      Mariusz

      If this post helps, then please consider Accepting it as the solution.

      Please feel free to connect with me.
      LinkedIn

       

      • tobiasmcbride's avatar
        tobiasmcbride
        Icon for Helper III rankHelper III
        6 years ago

        Thanks for that however is saying that there's an expression error upon saving - OLE DB or ODBC error (we cannot convert the value null to type Date) - think it's because some of the end dates have 'null' in them so how is it best to fix this Mariusz ?