cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vgarlap
Post Patron
Post Patron

Need help with Running total in %

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

 

Capture.PNG

 

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 
2 ACCEPTED SOLUTIONS
amitchandak
Super User IV
Super User IV

@vgarlap , 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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

v-luwang-msft
Community Support
Community Support

H@vgarlap 

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:

v-luwang-msft_0-1614332569848.png

 

 

Wish  it is helpful for you!

 

Best Regard

Lucien Wang

 

View solution in original post

3 REPLIES 3
v-luwang-msft
Community Support
Community Support

H@vgarlap 

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:

v-luwang-msft_0-1614332569848.png

 

 

Wish  it is helpful for you!

 

Best Regard

Lucien Wang

 

View solution in original post

amitchandak
Super User IV
Super User IV

@vgarlap , 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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

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 . 
 
Capture.PNG
I am getting 7689 is each row , but the data is not categorized according to Dates mentioned. 
 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.