Need help with Running total in %

[Anonymous]

Hi Community,

I am looking for running total on %'s for the below chart.  I am unable to add May 2021's data to May 2022 and then May 2021+May 2022 data should be added to May 2023 ( Some thing like rolling measure) . 

I tried the below forumuale . However, it isnt giving the results i am looking for. 

Sales RT =

VAR MaxDate = "May 2023"

RETURN

CALCULATE (

SUM('Forecast Table'[count]),

'Goal Table'[Month Year]> "May 2021" && 'Goal Table'[Month Year] <= MaxDate,

ALL('Goal Table'[Month Year])

)

Thanks,

G Venkatesh 

[amitchandak · Accepted solution]

@Anonymous , You need date, with this month format this will not work

Sales RT =
VAR MaxDate = Date(2023,5,31)
RETURN
CALCULATE (
SUM('Forecast Table'[count]),
filter(All('Goal Table'), 'Goal Table'[Date]> date(2020,5,1) && 'Goal Table'[Date] <= MaxDate && 'Goal Table'[Date] <= Max('Goal Table'[Date])
))

with the current month format, you can get a date like this. In case you do not have one

Date = "01 " & [Month Year] //Change data type to date

[v-luwang-msft · Accepted solution]

Hi @Anonymous 

You could create a new table like your provided ,only two column(date,Count of process Name),then use the following dax to create a measure:

Sales RT =

VAR mindate =

    DATE ( 2020, 01, 01 )

VAR maxday =

    DATE ( 2023, 10, 15 )

VAR last =

    CALCULATE (

        SUM ( 'Forecast Table'[Count of Process Name]),

        FILTER (

            ALL ( 'Forecast Table' ),

               'Forecast Table'[Date format]<= MAX ( 'Forecast Table'[Date format] )

                && 'Forecast Table'[Date format] <= maxday

                && 'Forecast Table'[Date format]  >= mindate

        )

    )

RETURN

    last

final you will see the below:

Wish  it is helpful for you!

Best Regard

Lucien Wang

[amitchandak · Accepted solution]

@Anonymous , You need date, with this month format this will not work

Sales RT =
VAR MaxDate = Date(2023,5,31)
RETURN
CALCULATE (
SUM('Forecast Table'[count]),
filter(All('Goal Table'), 'Goal Table'[Date]> date(2020,5,1) && 'Goal Table'[Date] <= MaxDate && 'Goal Table'[Date] <= Max('Goal Table'[Date])
))

with the current month format, you can get a date like this. In case you do not have one

Date = "01 " & [Month Year] //Change data type to date

[Anonymous]

Hi Amit,

Thanks for the reply . I modified the DAX expression using the above shared code. (shown below) 

Sales RT =

VAR MaxDate = Date(2023,5,1)

RETURN

CALCULATE (

COUNT('Forecast Table'[Process Name]),

filter(All('Goal Table'), 'Goal Table'[Date format]> date(2021,5,1) && 'Goal Table'[Date format] <= MaxDate)

)

However, i am not getting the required output. I am not understanding where the problem is. I attached the screen shot below . 

I am getting 7689 is each row , but the data is not categorized according to Dates mentioned.