topic Visualize Data During Last 1 Year (Example: 01 June 2021 until 31 May 2022) in DAX Commands and Tips

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

agustira97 — Fri, 10 Nov 2023 09:19:19 GMT

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=105487146609591265590&rtpof=true&sd=true
*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)
)

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

Rupak_bi — Fri, 10 Nov 2023 11:18:33 GMT

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]))

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

agustira97 — Sun, 12 Nov 2023 12:55:09 GMT

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...