Convert Month to string like M1 depending on the filter

[Thomas94401]

Hi, 

I have a Sales table by month: 

Date	Option	Sales
Jan-2019	A	1
Jan-2019	B	2
Feb-2019	A	3
Feb-2019	B	4
Mars-2019	A	5
Mars-2019	B	6

I want to build a groupby table summing sales. For example, an output I would like : 

If I filter on Date between Feb2019 and June2019 : I would like : 

Date	Sum  of sales
M1	7
M2	11

with :

- M1 = first month of the filter

- M2 = second month of the filter ...

Do you know if it is possible to build this column ? 

Thanks in advance, 

I tried everything and cannot obtain this result 😞

        

[Thomas94401]

Hi, Thanks a lot for your help ! 

I created this PBIX and the disconnected table. I was stuck on how you create the variable MonthFromSelection ?

[mahoneypat]

To do that, you need to create a disconnected table like the one below, and then in a variable in your measure calculate the month that would be that many MonthsFromSelection from the min date from the slicer.  You would then return your measure (sum in your case) for that month.

Month	MonthsFromSelection
M1	0
M2	1
M3	2

Pat

[Thomas94401]

Hi, Thanks a lot for your help ! 

I created this PBIX and the disconnected table. I was stuck on how you create the variable MonthFromSelection ?

[mahoneypat · Accepted solution]

I did it on an existing model, so you can adapt same approach.  I made the disconnected table called MonthTable.

And my Date table already had a column for the MonthEnding, so I used that in this measure to get the result shown.

SlicerMonth =
VAR slicermon =
    MIN ( MonthTable[MonthsAway] )
VAR EOMminslicerdate =
    EOMONTH ( MIN ( 'Date'[Date] ), slicermon )
RETURN
    CALCULATE ( [Total Sales], 'Date'[MonthEnding] = EOMminslicerdate )

Pat