Dates mess

[Anonymous]

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 need and the dates table:

My dates columns:

So, e.g, I'm trying to count candidates added last month:

CandidatesLast30 = CALCULATE(COUNT(Assignments[CandidateId]); PREVIOUSMONTH(Dates[Date].[Date]))

But it gives me blank result.

This gives me an error:

CandidatesLast30 = CALCULATE(COUNT(Assignments[CandidateId]); PREVIOUSMONTH(Assignments[DateAddedCandidate]))

I've also tried this without results:

CandidatesLast30 = CALCULATE(COUNT(Assignments[CandidateId]); USERELATIONSHIP(Assignments[DateAddedCandidate]; Dates[Date]);DATESYTD(Dates[Date].[Date]))

Can any one point me in the direction of what I'm doing wrong?

[kcantor]

@Anonymous 

Sometimes you need to go from the most basic calculation to determine where the failure it. I would suggest building this in blocks. It is tempting to roll all of the logic into a single calculation but in situations where something breaks it is harder to find the issue. Plus, in measure, other than appearing in the field lists, you aren't really losing performance for having them available to build agains.

I would suggest first building a count and using it to create more defined logic.

CandidatesLast30 = CALCULATE(COUNT(Assignments[CandidateId]); PREVIOUSMONTH(Dates[Date].[Date]))

Candidates Added = COUNT(Assignments[CandidateID])   -- side note, in my data I would use DISTINCTCOUNT

Drop that calculation onto a table with the months as columns and see if it works. 

Does it work? Do you get the result you should? If yes, build a calculation for the previous month. I personally use DATEADD instead of the wrappers like PREVIOUSMONTH as I am old school.

Candidates Added Last 30 = CALCULATE([Candidates Added], DATEADD(Dates[Date], -30, Days))

Drop that on your table as well. Does it give you the last months total in the current month? 

Keep in mind that the filter for month needs to be set in the table or by the page for this to work. If you have just a card, you need to define current month in order for last month to attribute correctly.

If these measures work, you can build them into a single measure if you prefer using Var like this:

Candidates Added Last Month =

     Var CandidatesAdded = COUNT(Assignments[CandidateID])

     Return

     CALCULATE([CandidatesAdded], DATEADD(Dates[Date], -30, Days))

[Anonymous]

Hi @kcantor  and thanks for contributing!

When counting all the candidatesID, i get 2845, or 1532 when using distinctcount.

Candidates Added = COUNT(Assignments[CandidateID]) 

But when adding the dates, I get some issues. Days does not work for me, I get an error. I need to use day.

Candidates Added Last 30 = CALCULATE([Candidates Added]; DATEADD(Dates[Date]; -30; DAY))

Still, I get 2845 from that ones as well.

[kcantor]

@Anonymous 

     *When counting all the candidatesID, i get 2845, or 1532 when using distinctcount.

Which number is correct?My assumption would be that distinct would give you the correct number. 

     *Days does not work for me, I get an error. I need to use day.

Yes, sometimes when typing in this forum I use the plural because I get in a hurry. Sorry, my bad.

If you use distinct count and DAY and put the month on the columns, does the number for last 30 day give you the same number as the distinct count for the previous month? (Which makes me question if you want month over month shuold we use -1, MONTH in the calculation instead)

Can you share any data or a file?

Also, have you used this resource before? It may be a better method for you.

https://powerbi.tips/2016/07/measures-month-to-month-percent-change/

[Anonymous]

@kcantor 

1532 is correct. When I get the dates correct, I'm going to match it with the ID in another table.

I could share the file with you in a private message? And we can share the solution here if we find it 🙂

[kcantor · Accepted solution]

@Anonymous 

Adding a relationship between tables and creating calculations using those relationships will solve this issue. Here is a group of relationship blogs to help.

https://powerpivotpro.com/category/skill-areas/relationships/

[kcantor]

@Anonymous 

You can share the file with me in a private message or if you have a link to a drop box you could share the data that way. I am always happy to help.

[Anonymous]

@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]

Hi @Anonymous ,

Thanks for answering.

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

[Anonymous]

@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]

---

@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]

@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.

[Anonymous]

@Anonymous 

Thanks agan.  I see it works in your file. but mine is still blank. So it needs to be a fundemental error somewhere.

My dates table: