Cummulative bar chart to hide set of certain months and display last 6 months data.

[Sarath5c8]

Was trying to display cummulative count of the id by months but i have almost 3+ yrs data but i need to show last 6 month trend.

If i  do filter on 

id_Cum_Cnt = CALCULATE(

    'tst'[id_count],

    FILTER(

        ALL('tst),

        'tst'[CREATE_DATE] <= MAX ('tst'[CREATE_DATE])

    )

)
and showing cumulative only for these 6 months, 
Need help to hide the other months data and still consider instead of starting from 0. 

[v-rzhou-msft · Accepted solution]

Hi @Sarath5c8 ,

There is something wrong in parry2k's code. I suggest you to try this measure to achieve your goal.

id_Cum_Cnt =
VAR __within6months =
    DATEDIFF (
        MIN ( 'tst'[CREATE_DATE] ),
        EOMONTH ( MAXX ( ALL ( tst ), tst[CREATE_DATE] ), 0 ),
        MONTH
    ) < 6
RETURN
    IF (
        __within6months,
        CALCULATE (
            'tst'[id_count],
            FILTER ( ALL ( 'tst' ), 'tst'[CREATE_DATE] <= MAX ( 'tst'[CREATE_DATE] ) )
        )
    )

My Sample has data from 2020/01/01 to 2022/08. Result is as below.

Best Regards,
Rico Zhou

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

[parry2k]

@Sarath5c8 first and foremost when you are working with dates, it is a best practice to add a calendar dimension to your model, you can check the time intelligence series on my YouTube channel https://www.youtube.com/watch?v=BKzsCfr4qeQ&list=PLiYSIjh4cEx2FkuGkngYruS0wm8MYFsoi&index=12

to return blank prior to 6 months, you can do something like this:

d_Cum_Cnt = 
VAR __within6months = DATEDIFF ( MAX ( MAX ('tst'[CREATE_DATE] ), TODAY (),  Month )  >= 6
RETURN
IF ( __within6months ),
CALCULATE(
    'tst'[id_count],
    FILTER(
        ALL('tst),
        'tst'[CREATE_DATE] <= MAX ('tst'[CREATE_DATE])
    )
)
)

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!