Show Data from last 3 months (based on slicer)

[plona95]

Hi, I would like to create a clustered column chart like this:

Where for each column (X axis is a machine) the first column shows a value for 1 month ago, second 2 months ago, and third 3 months ago. If a user set a yearmonth slicer to April 2025, the first column should show March 2025, second - Feb 2025, third - Jan 2025.

Here is how my data looks like:

It's already unpivoted. 

Could you please help? Thank you!

[Ashish_Mathur · Accepted solution]

Hi,

Try this approach.

1. Create a Calendar Table with calculated column formulas for Year, Month name and Month number. Sort the Month name column by the Month number.
2. Create a relationship (Many to One and Single) from the Date column of the Fact table to the Date column of the Calendar Table
3. To your visual, drag the Year and Month name from the Calendar Table
4. Write these measures

Total = sum(Data[Value])

Total in PM = calculate([Total],previousmonth(calendar[date]))

Total in P2PM = calculate([Total in PM],previousmonth(calendar[date]))

Total in P2P2PM = calculate([Total in P2PM],previousmonth(calendar[date]))

Hope this helps.

[plona95]

That seems to be it!

[ryan_mayu]

@plona95 

you can create a measure

measure =

var _max=EDATE(max('date'[Date]),-1)

var _min=EDATE(_max,-3)+1

return if(max('Table'[date])>=_min&&max('Table'[date])<=_max,1)

and add this measure to visual filter and set to 1

pls see the attachment below

[plona95]

not working

[Demert]

Hi @plona95  

I suggest the following solution :

Let's say you have a filter for year and for month. We will have to call them up to know what the value is. We put it in a date format and to make it easy we always take the first day of the month. Next up we calculate the end of month -1 (previous month) then we take the sum of the value where the year and month equal the selected prior month. And we remove the dim date filter so we aren't restricted to april.

PYMonth = 
var _EOMonth = EOMONTH(DATE(SELECTEDVALUE('DIM Date'[Year]),SELECTEDVALUE('DIM Date'[MonthNumber]),1),-1)
return
 CALCULATE(SUM('Table'[Value]),REMOVEFILTERS('DIM Date'), YEAR(_EOMonth)= YEAR('Table'[Date]),month(_EOMonth)= month('Table'[Date]))

PYMonth-1 = 
var _EOMonth = EOMONTH(DATE(SELECTEDVALUE('DIM Date'[Year]),SELECTEDVALUE('DIM Date'[MonthNumber]),1),-2)
return
 CALCULATE(SUM('Table'[Value]),REMOVEFILTERS('DIM Date'), YEAR(_EOMonth)= YEAR('Table'[Date]),month(_EOMonth)= month('Table'[Date]))

PYMonth-2 = 
var _EOMonth = EOMONTH(DATE(SELECTEDVALUE('DIM Date'[Year]),SELECTEDVALUE('DIM Date'[MonthNumber]),1),-3)
return
 CALCULATE(SUM('Table'[Value]),REMOVEFILTERS('DIM Date'), YEAR(_EOMonth)= YEAR('Table'[Date]),month(_EOMonth)= month('Table'[Date]))

[Elena_Kalina]

Hi @plona95 

// Dynamic measure for 1 month ago
Value 1 Month Ago = 
VAR SelectedDate = SELECTEDVALUE('Date'[Date], MAX('Date'[Date])) // Gets selected date or most recent
VAR OneMonthAgo = EOMONTH(SelectedDate, -1)
VAR FirstDayOfMonth = EOMONTH(OneMonthAgo, -1) + 1
VAR LastDayOfMonth = OneMonthAgo
RETURN
CALCULATE(
    SUM('YourTable'[Value]),
    FILTER(
        ALL('Date'),
        'Date'[Date] >= FirstDayOfMonth &&
        'Date'[Date] <= LastDayOfMonth
    )
)

// Dynamic measure for 2 months ago
Value 2 Months Ago = 
VAR SelectedDate = SELECTEDVALUE('Date'[Date], MAX('Date'[Date]))
VAR TwoMonthsAgo = EOMONTH(SelectedDate, -2)
VAR FirstDayOfMonth = EOMONTH(TwoMonthsAgo, -1) + 1
VAR LastDayOfMonth = TwoMonthsAgo
RETURN
CALCULATE(
    SUM('YourTable'[Value]),
    FILTER(
        ALL('Date'),
        'Date'[Date] >= FirstDayOfMonth &&
        'Date'[Date] <= LastDayOfMonth
    )
)

// Dynamic measure for 3 months ago
Value 3 Months Ago = 
VAR SelectedDate = SELECTEDVALUE('Date'[Date], MAX('Date'[Date]))
VAR ThreeMonthsAgo = EOMONTH(SelectedDate, -3)
VAR FirstDayOfMonth = EOMONTH(ThreeMonthsAgo, -1) + 1
VAR LastDayOfMonth = ThreeMonthsAgo
RETURN
CALCULATE(
    SUM('YourTable'[Value]),
    FILTER(
        ALL('Date'),
        'Date'[Date] >= FirstDayOfMonth &&
        'Date'[Date] <= LastDayOfMonth
    )
)

[plona95]

not working