Forum Discussion

PowerUser2000's avatar
PowerUser2000
Icon for Helper I rankHelper I
1 year ago
Solved

How do I get a running total to work?

Hello, I am trying to capture the running total captured here:

Running_Total_by_AccessAndDate =
VAR CurrentAccessType =
SELECTEDVALUE('PING_USER_SIGNON_NEW (2)'[useraccesstyp])

VAR CurrentMonth =
MAX('Calendar'[YearMonthNumber])

RETURN
CALCULATE(
COUNTROWS('PING_USER_SIGNON_NEW (2)'),
FILTER(
ALL('Calendar'),
'Calendar'[YearMonthNumber] <= CurrentMonth
),
'PING_USER_SIGNON_NEW (2)'[useraccesstyp] = CurrentAccessType
)

However, this is not working properly. As it is not giving me the correct outputs. The outputs are much lower than expected.

I have a date table called Calender than has date, month_year_label (Mar-25), and YearMonthNumber (202501). Then I have my user sign on table. That I am connect to the date table on YearMonthNumber to Yearmonth in the user sign on table that is also number format (202501).

This measure was working when I had no date table being used that was connected to it for reference:

Running_Total_by_AccessAndDate 3 =
VAR CurrentAccessType = SELECTEDVALUE('PING_USER_SIGNON_NEW (2)'[useraccesstyp])
VAR MaxDateInContext = MAX('PING_USER_SIGNON_NEW (2)'[CREATEDATE])
VAR MinDateInContext = CALCULATE(MIN('PING_USER_SIGNON_NEW (2)'[CREATEDATE]), ALL('DIM_DATE'))
RETURN
CALCULATE(
    COUNTROWS('PING_USER_SIGNON_NEW (2)'),
    FILTER(
        ALL('PING_USER_SIGNON_NEW (2)'),
        'PING_USER_SIGNON_NEW (2)'[useraccesstyp] = CurrentAccessType &&
        'PING_USER_SIGNON_NEW (2)'[CREATEDATE] <= MaxDateInContext
    ),
    VALUES('DIM_DATE'[year_month_number])
)




Need Help
  • v-dineshya's avatar
    v-dineshya
    1 year ago

    Hi PowerUser2000 ,

    Please share the sample PBIX file, Do not include sensitive information.  It will help me to replicate  the scenario.

     

    Regards,

    Dinesh

7 Replies

    • PowerUser2000's avatar
      PowerUser2000
      Icon for Helper I rankHelper I
      1 year ago

      Hello, I am trying to create a bar chart visual like this. However, it is only capturing the running total by each month I think the totals are way too low. 

       

      • v-dineshya's avatar
        v-dineshya
        Icon for Community Support rankCommunity Support
        1 year ago

        Hi PowerUser2000 ,

        Please share the sample PBIX file, Do not include sensitive information.  It will help me to replicate  the scenario.

         

        Regards,

        Dinesh

  • v-dineshya's avatar
    v-dineshya
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi PowerUser2000 ,

    Thank you for reaching out to the Microsoft Community Forum.

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
    Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
    Please show the expected outcome based on the sample data you provided.


    Regards,

    Dinesh

    • v-dineshya's avatar
      v-dineshya
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi PowerUser2000 ,

      We haven’t heard from you on the last response and was just checking back to see. Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided.

       

      Regards,

      Dinesh

      • v-dineshya's avatar
        v-dineshya
        Icon for Community Support rankCommunity Support
        1 year ago

        Hi @PowerUser2000 ,

        We haven’t heard from you on the last response and was just checking back to see. Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided.

         

        Regards,

        Dinesh