Forum Discussion

A-IT's avatar
A-IT
New Member
5 years ago
Solved

Wrong Count in Month

Good morning. I am attempting to create a Power BI report. All has worked well so far. I've used DAX for some functions (such as cumulative total per month), I've created my own Date table, and the other usual Power BI functions. I'm having an issue with a specific graph. I am trying to show the values of closed items per month. All is well when I create it initially, the total shows for as 214 for the years 2019 and 2020.
When I filter to 2020, the total is still correct, showing a value of 89.

When I drill down into the months, this is were the incorrect values occur. May is showing one closure, which I know is not the case.Here you can see my data set filtered for May 2020, showing 6 values.

What is interesting is if I filter the data set in "Transform Data" by May, and go back to the graph, it is showing 5 of these May closures in April and 1 in May.Currently I'm at a loss of how to fix  this error. Everything else on the dashboard appears to be working. I initial used my the Count of my Closure Days calculated field to create the graph. I also switched the Values field to the Closure Date itself and used the count. The same error appears. Thank you for taking a look and the assistance. I'm sure it's a simple oversight, but I can't find it currently.

Need Help
  • A-IT's avatar
    A-IT
    5 years ago

    Thank you for the reply, I'm still learning. I did not realize that you could only have one active connector. I changed the connector to the Closure Date, and this did fix the issue. How ever, I created another chart that had a cumulative total per month using the following measure:

    Cumulative QAIL =
    CALCULATE(
    SUM('QAIL AQMS_410-40-018'[QAIL Count]),
    FILTER(
    ALLSELECTED('QAIL AQMS_410-40-018'),
    'QAIL AQMS_410-40-018'[Created] <= MAX('QAIL AQMS_410-40-018'[Created])
    ))
    This had an error when Closure Date was used, as it showed a large column with the value of "Blank" besides the months. I fixed the issue by creating another Date Table, and using one as a connector to the Created Date, and one as a connector to the Closure Date. I'm not sure if this is the "best" solution, but it worked for the time being!

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Is the relationship between date table and fact table on closure date?

    You need to check that.

     

    Appreciate your kudos!! Mark my post as solution if this helps.

    • A-IT's avatar
      A-IT
      New Member
      5 years ago

      I do have relationships. Closure Date, Created, and Time to Closure are related to Date in the Date Table.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        You have 3 relationships out of which one is active.

        Check which one is that, and make it active with closure date.

         

        Appreciate your kudos!! Mark my post as solution if this helps.

  • MEFOX's avatar
    MEFOX
    Icon for Helper III rankHelper III
    5 years ago

    I have a very similar problem were the totals change when I drill down from the Quarter to the Months in the Quarter. These are the two fields that I'm using in the visual. 

    TotalAccounts = if(
    ISBLANK(
    DISTINCTCOUNTNOBLANK('CRM FactOpportunity'[AccountID]))=TRUE(),
    0,
    DISTINCTCOUNTNOBLANK('CRM FactOpportunity'[AccountID]
    )
    )
     

    and 

     

    TargetSales = (CALCULATE([TotalBudget],'Shared DimBudget'[OpportunitiyType]="Membership") /12)
     
    Would the way these two measures are coded be the problem?