cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Roha83 Member
Member

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

relationship.jpg

My dates columns:

datecolumns.jpg

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
kcantor Super Contributor
Super Contributor

Re: Dates mess

@Roha83 

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/



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

12 REPLIES 12
nsrshkh1 Member
Member

Re: Dates mess

@Roha83 ,

 

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.

Roha83 Member
Member

Re: Dates mess

Hi @nsrshkh1 ,

Thanks for answering.

 

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

Highlighted
nsrshkh1 Member
Member

Re: Dates mess

@Roha83 ,

 

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?

 

 

Roha83 Member
Member

Re: Dates mess


@nsrshkh1 wrote:

@Roha83 ,

 

Can't give solution without looking data

1) Check Data Types of Date Columns

 - they are of type "Date"

 

datestype.jpg

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:

datesandcandidates.jpg

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

I removed all the relationships now:

relationship.jpg

 

columns in assignment table:

assignmenttable.jpg

 

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.

 

 

 

 


 

nsrshkh1 Member
Member

Re: Dates mess

@Roha83 

 

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.

 

 

Roha83 Member
Member

Re: Dates mess

@nsrshkh1 

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:

datestable.jpg

kcantor Super Contributor
Super Contributor

Re: Dates mess

@Roha83 

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))

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Roha83 Member
Member

Re: Dates mess

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 Super Contributor
Super Contributor

Re: Dates mess

@Roha83 

     *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/



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 86 members 2,847 guests
Please welcome our newest community members: