Visualize Data During Last 1 Year (Example: 01 June 2021 until 31 May 2022)

[agustira97]

I have monthly data that will be updated every first day of the month.
I have a case, to visualize data during 1 last year, for example when slicer year = 2022 and month = 5, it will take data from 01 June 2021 until 31 May 2022. How to make dax to provide this case?

I tried to make dax on my Visual Studio and it looks like this:
Last1Year :=
VAR Select_Month = MONTH(MAX(Test[postingdate]))
VAR Select_Year = YEAR(MAX(Test[postingdate]))
VAR StartDate = DATE(Select_Year - 1, Select_Month + 1, 1)
VAR EndDate = EOMONTH(StartDate, 11)
RETURN
CALCULATE(
ABS(SUM(Test[value])),
FILTER(
Test,
Test[value] >= StartDate &&
Test[value] <= EndDate
)
)

but unfortunately, the output visual on my pbix when we put slicer year = 2022 and month = 5, it will just take value on May 2022.
Can you help me to correct my dax? Thank you.

*Excel Link: https://docs.google.com/spreadsheets/d/1nOkOOtVMdu0SXG21W5PR9dsyN42OQ_la/edit?usp=sharing&ouid=10548...
*This data have a relationship with calculated table DIM_DATE, here's the dax for it:
DIM_DATE := ADDCOLUMNS(
CALENDAR(DATE(2018,1,1),EOMONTH(TODAY(),-1)),
"day", DAY([Date]),
"week", WEEKNUM([Date]),
"month", MONTH([Date]),
"monthname", FORMAT([Date],"mmm"),
"quarter", FORMAT([Date],"q"),
"quartername", "Q"&FORMAT([Date],"q"),
"year", YEAR([Date]),
"lastmonth", EOMONTH(TODAY(),-1)
)

[Rupak_bi]

Hope you are looking for below result. Your formula is correct. only you need to use selected value. Else you may use below formula.

new value =

calculate(SUM(Test[value]),

all(DIM_DATE),

DIM_DATE[Date]>=date(SELECTEDVALUE(DIM_DATE[Date].[Year])-1,SELECTEDVALUE(DIM_DATE[Date].[MonthNo])+1,1),

DIM_DATE[Date]<=max(DIM_DATE[Date]))

[agustira97]

Thank you for the solution, but what missing is, from your solution you are using 1 slicer, right now I using 2 different slicer Year and Month, so what if the case right that?
Hope you can also help me for this case...