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

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