Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Dates mess

Hi, I have a table with several dates columns that I need to use. But I'm not getting the results I want. I've created a dates tabel, and created a relationship between the dates columns that I nee...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Anonymous ,

 

1) Remove relations between Dates and Assignment Table

2) Use below method to calculate measures.

CandidatesLast30 =
CALCULATE (
    SUMX (
        ADDCOLUMNS (
            Dates,
            "Total", CALCULATE (
                COUNTA ( Assignments[CandidateId] ),
                FILTER ( Assignments, Dates[Date] = Assignments[DateAddedCandidate] )
            )
        ),
        [Total]
    ),
    FILTER (
        Assignments,
        MONTH ( Assignments[DateAddedCandidate] )
            = MONTH ( TODAY () ) - 1
    )
)

 For creating  other measures you can edit Red and Green areas as per your requirement.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Anonymous ,

Thanks for answering.

 

I tried removing the connection and applying your code, but it still gives me blank results?

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Anonymous ,

     

    Can't give solution without looking data

    1) Check Data Types of Date Columns

    2) Check whether you have used proper columns in formula.

    3) Post a snap of relationship pane and data table so that I can have a look

     

    You want count of Candidates based on DateAddedCandidate column for last month, right?

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago
      Anonymous wrote:

      Anonymous ,

       

      Can't give solution without looking data

      1) Check Data Types of Date Columns

       - they are of type "Date"

       

      2) Check whether you have used proper columns in formula.

      I've tried this with assignments as well. counting assignments ytd. using the datesytd function. Same type of result.

      I've checked the data and it is indeed candidatesID's to be counted in the selected periode:

      3) Post a snap of relationship pane and data table so that I can have a look

      I removed all the relationships now:

       

      columns in assignment table:

       

      You want count of Candidates based on DateAddedCandidate column for last month, right?

      Yes. I have a lot of stuff I want to show based on these data. assignments that starts ytd, mnt. assignments ends ytd, mtd. candidates added last 3 months. etc etc. But all gives me strange results.

       

       

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Anonymous 

         

        Thanks for the detailed explanation!

         

        Now check whether Date Table you created have dates from previous months (This is most important and basic).

         

        https://1drv.ms/u/s!Ah0UhoGdkH5biiOjPbQ82ZBihJZY?e=K3ymgI

         

        This is what I tried, and I am getting it correct! Let me know your findings.

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.