Sum between two months

[IF]

Hi,

I have a Data table(Main), Calendar Table (Month), and Type table (Type). I use two slicers; one for type selection from the Type table and the other one is for the month selection from the Month table.

I want to calculate the sum for the selected type with the condition: If 02.2020 is selected and type b is selected, it should bring the sum between 12.2019 (inclusive) and 10.2019 (inclusive). Similarly; if I select 05.2020, the sum should be between 03.2020 and 01.2020.

I wrote this measure and it brings data starting from the selection month to 2 months backward which I don't want:

selected type moving sum =

CALCULATE(SUM(Main[value]),FILTER(ALL(Main),'Main'[dateselection]<=SELECTEDVALUE('Month'[Month])&&'Main'[dateselection]>=CALCULATE(MIN('Month'[Month]),DATEADD('Month'[Month],-2,MONTH))&&'Main'[type]=SELECTEDVALUE('Type'[type])))

I have also the list to correspond to the result, but it is also not in the way that I am looking for. I uploaded the file to:

https://www.dropbox.com/s/t9mbm0x1p3l1eti/sum%20between2.pbix?dl=0

Can you help me how to show data between -2 and -5 months based on the selection?

Thanks a lot in advance!

[IF]

Hi, thanks for the measure. it works. Is it possible to see the values as a table? For example; if I select 02.2020 and type "b", I want to see the list:

b	12.2019	5
b	11.2019	2
b	10.2019	4

thanks in advance

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[IF]

Thank you very much! How can I show those in a table as a list? regards,

[Ashish_Mathur]

Hi,

Not sure of how to do that.

[IF]

Hi, I used the measure in a line chart to show the values for each month and it didn't work. Do I do stmh wrong? Regards,

[Ashish_Mathur]

Hi,

My measure will return a single scalar value.  That is what i thought your original requirement was.  Now i do not know what you want.

[AllisonKennedy]

@IF Is your month table a date table? https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

Is your Type table related to Main table? It should be, so you don't need this filter included in your formula. https://excelwithallison.blogspot.com/search?q=complicated 

If so, try using DAX DATESINPERIOD https://dax.guide/datesinperiod/ :

selected type moving sum =
VAR _selectedMonth = MAX(Dates[Date])

VAR _MonthsEnd = -2

VAR _PeriodLength = -3

VAR _endDate = EOMONTH(_SelectedMonth, _MonthsEnd)

RETURN

CALCULATE(SUM(Main[value]), DATESINPERIOD( Dates[Date] , _endDate, _PeriodLength, MONTH )

[IF]

Hi, if I am doing it correctly, the measure doesn't show the value in a line chart to show values for each month. line. Can you test as well? Thanks 🙂