Count items YTD

[Anonymous]

Hi,

I'm new to power bi and are facing some challenges.

I'm trying to count the number of items in a table this year.

So I have CandidateID that I want to count. And I have StartDate which I want to have as a date filter. I'm using Dax to avoid the report/page level filters since I'm going to have several date ranges on my dashboard.

I've managed to get this to work with the GUI reports, but as mentioned above I want to solve it with dax.

So here is my code:

CandidateId YTD =

    TOTALYTD(

        COUNT('Assignments'[CandidateId]);

        'Assignments'[StartDate].[Date]

    )

This returns 1, not 463 as it should.

The startDate is formatted like this:

dd/mm/yyy tt:mm:ss

I have time intelligence enabled

[v-juanli-msft]

Hi @Anonymous 

"sheet 3" is "Assignments" table,

Your formula is incorrect.

Try create another column in "Assignments" table,

Column_date = DATE(YEAR(Assignments[date]),MONTH(Assignments[date]),DAY(Assignments[date]))

Use this column in the measure

Measure = TOTALYTD(COUNT(Assignments[id]),Assignments[Column_date],ALL(Assignments[date]))

After select "ignore" icon on the "card" or "table" visual where [Measure] lies in as said in 

Reference:

Change how visuals interact in a Power BI report,

It should show the value in a card visual correctly.

Please download my new pbix file.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Maggi,

Thanks again for your answer.

I'm still getting some errors with your code:

When creating the column it says "Column 'date' in table 'Assignments' cannot be found or may not be used in this expression. However, if I use:

column_date = date(year(assignments[startdate].[date]) ... etc, it accepts it.

And again, when trying to make the meassure, it complaints:

error on assignements[id]  -  But I guess you mean assigments[candidateid] ?

error on ALL(assignements[date]) - Columnd 'date' in table assignemtns cannot be found

But since last i've come a bit longer.

This one seems to work for me:

candidateytd=

CALCULATE(

COUNT('Assignments'[CandidateId]);

DATESYTD(Assignments[StartDate].[Date] < TODAY()))

And for mont to date:

CandidateMTD =

CALCULATE(

COUNT('Assignments'[CandidateId]);

DATESMTD(Assignments[StartDate].[Date] < TODAY()))

It gives me an error "parameter is not the correct type", but it still works.

Also, any idea why this doesn't work? In my head it should give me the same result:

mtdtest =

CALCULATE(

COUNT('Assignments'[CandidateId]);

DATESMTD(Assignments[StartDate].[Date]); FILTER('Assignments'; 'Assignments'[StartDate].[Date] < TODAY())

)

[v-juanli-msft]

Hi @Anonymous 

1. Create a calendar table

calendar = CALENDARAUTO()

2. Create a calculated column in 'Assignments' table

Column_date = DATE(YEAR(Sheet3[date]),MONTH(Sheet3[date]),DAY(Sheet3[date]))

3. Create relationship between "calendar' and 'Assignments' table based on [Date] and [Column_date] table

4. Create a measure in 'Assignments' table

Measure = TOTALYTD(COUNT(Sheet3[id]),'calendar'[Date],ALL(Sheet3[date]))

5.On the page, add [Date] from "calendar" table in the slicer, select "ignore" icon from "Table" visual and "card" visual for "slicer" visual.

Reference:

Change how visuals interact in a Power BI report

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for your answer Maggie. I still was not able to get it to work as I wanted. I got an error "sheet 3" does not exist.

So I tried making own columns for year, quarter, month and day in my existing table. So now I have a hiearky named year. And now, if I count it shows me everything from all years, with this code:

CandidateId YTD =

TOTALYTD(

COUNT('Assignments'[CandidateId]);

'Assignments'[Year].[Date]

)

Is it a way to have it select current year?