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 o...
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!
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.

  • A-IT's avatar
    A-IT
    New Member
    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!
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Great, it worked.

       

      Please appreciate a kudo to my post and accept the post as solution

      • 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?