Hide months Without Affecting Values in cumulative line chart - based on a filter

[Anonymous]

Hello,

I have a cummulative line chart using the following calculations:

     Submitted_running = calculate(sum([SubmittedCR]), FILTER(ALL(Orders[Date]),Orders[Date]<=max(Orders[Date])))

All data is on table [Orders].

 

As you can see, it starts back in 2016. What I would like to achieve is to place a filter (slicer) that displays only last 3-6-12 months of data. Because I have the cummulative sum, I cannot just ad a filter by [Date] as if will affect the calculations.

Thank you,

M.R.

[v-jayw-msft]

Hi @Anonymous ,

Please create a measure as below and add it to visual level filter.

Measure 2 = IF(FORMAT(SELECTEDVALUE('Table'[date]),"YYYYMM")>FORMAT(EDATE(TODAY(),-SELECTEDVALUE('Table (2)'[Column1])),"YYYYMM"),1,BLANK())

 

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Anonymous]

Thank you but plese be aware that I have a cummulative sum and I can not just filter previous data. I tried your approach, it does not work

[amitchandak · Accepted solution]

Create a date calendar and join with your date. Move this calculation there and try the filter

Submitted_running = calculate(sum([SubmittedCR]), FILTER(ALL(Date),Date[Date]<=max(Date[Date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Anonymous]

Hi amit chandak,

I figured it out:

calculate(sum([SubmittedCR]), FILTER(ALL(Calendar[Date]),Calendar[Date]<=max(DataTable[Date])))

Accepted as solution.

Thanks,

M.R.

[Anonymous]

I created a Calendar table but I am not able to get this to work.

Lets's say my table is called [DataTable] and the calendar table is called [Calendar]

Is this is the calculation?

Submitted_running = calculate(sum([SubmittedCR]), FILTER(ALL(Calendar),Calendar[Date]<=max(Calendar[Date])))

[Anonymous]

Yes, I can probably do that but is there any other simple solution without creating a Calendar?