Timeline & Cumulative Filter

[NewPBIe]

Hello everybody, 

I have a Timeline and a Table.

The Table has a Date-Column and a Score-Column. 

If i select the fiscal period 1 (october 22), the table shows the data, that are in october 22. 

I need to show data from the beginning up to september 22.

Also: for every period p, all the previous months until p-1.

Is this possible? Thank you guys!

[v-yiruan-msft · Accepted solution]

Hi @NewPBIe ,

One date dimension table should be exist in your model. And the date table don't create relationship with your fact table. You can refer the following blogs to achieve it.

Show data for the last N months based on a selected month

Sales N last Months =
VAR toDate =
    MAX ( 'Date'[Date] )
VAR fromDate =
    EDATE ( toDate, -1 * 'Previous months'[Previous months Value] ) + 1
RETURN
    CALCULATE (
        Sales[Sales Amount],
        ALL ( 'Date' ),
        DATESBETWEEN ( Date_Helper[Date], fromDate, toDate )
    )

Display Last N Months & Selected Month using Single Date Dimension in Power BI

If the above one can't help you get the desired result, please provide some raw data in your table  (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[amitchandak]

@NewPBIe ,

Last month  + MTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-2)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

This Month+Last Month  =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)
var _min = eomonth(_max,-2)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

In case you want display Sep /Oct on Axis, then slicer need to be on an independent date table

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -2) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI