Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Count items YTD

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

 

Need Help

4 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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?
  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

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