Running totals based on date

[Anonymous]

Hi,

Please find the attached sample data, the requireent is to display the running totals based on month filter selection. 

Please find the attachment with requirement explained.https://drive.google.com/file/d/12hOoElZXbH0ZhKWUqOQL7nOItxITkMDa/view?usp=sharing 

Regards.

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

In my opinion, it is better to have a disconnected calendar table, if the slicer is acting differently with the visualization.

Please check the below picture and the sample pbix file's link down below.

The measures are newly written, and those are in the sample pbix file.

https://www.dropbox.com/s/4jddj5vsnjt8qkt/Finance%20Sample%20Data%20-%20Copy.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[v-xiaotang · Accepted solution]

Hi @Anonymous 

You can take steps bellow for reference:

1. Create a calendar table

Calendar Table = SUMMARIZE( financials, financials[Date])

Keep them disconnected：

1. Create the measures：

myMeasure =
VAR _max = SELECTEDVALUE('Calendar Table'[Date])
VAR _firstdate =
    CALCULATE (
        FIRSTDATE ( financials[Date] ),
        FILTER ( ALL ( financials ), financials[Date].[Year] = YEAR ( _max ) )
    )
RETURN
    IF (
        SELECTEDVALUE ( financials[Date] ) > _max,
        BLANK (),
        CALCULATE (
            SUM ( financials[ Sales] ),
            KEEPFILTERS (
                FILTER (
                    ALL ( financials ),
                    financials[Date] >= _firstdate
                        && financials[Date] <= _max
                )
            )
        )
    )

result:

Hope it helps.

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

In my opinion, it is better to have a disconnected calendar table, if the slicer is acting differently with the visualization.

Please check the below picture and the sample pbix file's link down below.

The measures are newly written, and those are in the sample pbix file.

https://www.dropbox.com/s/4jddj5vsnjt8qkt/Finance%20Sample%20Data%20-%20Copy.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM