Forum Discussion

CJ_96601's avatar
CJ_96601
Helper V
1 year ago
Solved

Measure

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

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

2 Replies

  • freginier's avatar
    freginier
    Solution Sage
    1 year ago

    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!

    😁😁

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.