Measure

[CJ_96601]

Support needed to create measure on how to show 6 months from previous year and 6 months from current year based on year selection

[Anonymous · Accepted solution]

Hi @CJ_96601 

Based on your needs, I have created the following table.

Then you can use the following measure to get the result you want.

Previous 6 months current year = 
VAR _Yearnum = YEAR(TODAY())
VAR _Monthnum = MONTH(TODAY())
VAR _total_sales = SUMX(FILTER(ALL('Table'),MONTH('Table'[Date]) >  _Monthnum-6 && MONTH('Table'[Date]) <= _Monthnum  && YEAR('Table'[Date]) = _Yearnum),'Table'[Sales])

RETURN _total_sales

Previous 6 months last year = 
VAR _Yearnum = YEAR(TODAY())
VAR _Monthnum = MONTH(TODAY())
VAR _total_sales = SUMX(FILTER(ALL('Table'),MONTH('Table'[Date]) >  _Monthnum-6 && MONTH('Table'[Date]) <= _Monthnum  && YEAR('Table'[Date]) = _Yearnum-1),'Table'[Sales])

RETURN _total_sales

Result:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[freginier]

Hi there!

If I understand correctly, you need a DAX measure to display 6 months from the previous year and 6 months from the current year based on a selected year. In order to do this, you can use DATESINPERIOD and CALCULATE functions.

Here's a DAX formula you can try: 

Measure_6_Months =
VAR SelectedYear = SELECTEDVALUE('Date'[Year])

-- Get the first date of the selected year
VAR StartDate = DATE(SelectedYear - 1, 7, 1) -- Start from July of Previous Year
VAR EndDate = DATE(SelectedYear, 6, 30) -- End in June of Selected Year

RETURN
CALCULATE(
SUM('Sales'[Amount]), -- Replace 'Sales'[Amount] with your measure
FILTER(
ALL('Date'),
'Date'[Date] >= StartDate &&
'Date'[Date] <= EndDate
)
)

Hope this helps!

😁😁