Power BI : Past N days in Matrix

[sahildoshi]

Hi All,

I have a requirement where I need to display past 30 days trend in Matrix visual based on selection done from date filter. For example, if 13th July is selected, then the Matrix should display dates from 13th July to 13th June in Descending order. Similarly, if 1st July is selected then Matrix should display dates from 1st July to 1st June.

Below is my sample dataset.

Below is the expected output :

I have tried below measure till now but no success. 

30 days Value =

var _value = CALCULATE(SUM('fact'[Sales]), ALL('date'[date]))

var _sel = SELECTEDVALUE('date'[date])

var _selected = CALCULATE(MAX('fact'[fact_date]), 'fact'[fact_date] = _sel, ALL('date'[date]))

var _mindate = _selected - 5

return CALCULATE(_value, 'fact'[fact_date] <= _selected && 'fact'[fact_date] > _mindate)

Current Output irrespective of what date I select in filter.

Please help me out here @GilbertQ @amitchandak @Greg_Deckler 

[Greg_Deckler]

@sahildoshi Maybe:

30 days Value =
var _sel = SELECTEDVALUE('date'[date])
var _mindate = _selected - 5
return SUMX(FILTER(ALL('fact'),[fact_date] <= _sel && 'fact'[fact_date] > _mindate),'fact'[Sales])

[sahildoshi]

Hi @Greg_Deckler , thank you for your response. I tried your measure but it just sums up sales of period in consideration. Please refer below snapshot when date selected is 12 Jul. It sums up sales from 9 Jul to 12 Jul and displays for all dates.

[Greg_Deckler · Accepted solution]

@sahildoshi I just realized what you actually want. PBIX file is attached below signature.

Selector = 
    VAR _sel = MAX('Calendar'[date])
    VAR _min = _sel - 5
    VAR _date = MAX('Transactions'[fact_date])
RETURN
    IF(_date >= _min && _date <= _sel,SUM(Transactions[Sales]),BLANK())

[sahildoshi]

Hi @Greg_Deckler , thank you your solution it works. So the key part was the relationship between the tables, in my case the fact and calendar was connected. I'll have to create a copy  of transaction table (disconnected table) for this visual then.

[Greg_Deckler]

@sahildoshi So where is the Date coming from in that table visual? From Date or Fact table? Also, is there a relationship between those two tables? If you can post the sample data you provided as text, I can mock up a quick PBIX and get it solved.