dax for 12 months sales needs to filter on multiple data's

[Avivek]

I have a requirement to show the last 12 month sales for which the dax used is 

Last N Month Sales =

---------------------------------------------------------

VAR _selectedAltYear =

SELECTEDVALUE( 'To Year'[Year Number])

-----------------------------------------------------

//new date table

VAR __rlvntDateTable =

CALCULATETABLE(

'Date',

FILTER('Date','Date'[Date]>= edate([Max Date of To Year],-12) && 'Date'[Date]<= [Max Date of To Year])

)

---------------------------------------------------------

VAR _altsalesamount =

CALCULATE( [Sales Amount], __rlvntDateTable )

RETURN

_altsalesamount

It works fine with the To Year slicer, however we also have system and segment slicer. For eg. If we select the To Year as 2022 then it shows sales from Feb 2021 to Dec 2021. But if we select some system or segment name who might not have had sales in the period Feb 2021 to Dec 2021then they show sales only for the month in the period Feb 2021 to Dec 2021 where the sales have happened for the given system or segment. 

Is it possible to filter the data based on all the three slicers, say To Year, System and Segment.

[ALLUREAN]

Hi, @Avivek 

Try this:

Last 12 Months =
CALCULATE (
[Sales Amount],
DATESINPERIOD (
'Date'[Date], -- returns period from date column
MAX ( 'Date'[Date] ), -- starting from MAX date
-12, -- shifting it back 12 intervals
MONTH -- each interval being a month
)
)

Did I answer your question? Please Like and Mark my post as a solution if it solves your issue. Thanks.

Appreciate your Kudos !!!

https://allure-analytics.com/

https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA

https://www.linkedin.com/company/77757292/

[Avivek]

Thankyou @ALLUREAN , the measure seems to work in general. But we have few more requirements that needs to be taken into account. We need to have a date slicer which was earlier from To Year  table. But it seems the user wants to have a current year slicer instead of the usual To Year slicer. The current year slicer would show the current year by default by that slicer.

But since I have To Year as a filter condition in the measure itself, I am unable to use Current Year column as a slicer for this page. Is there a way to show the current year below like shown in the image without changing anything in the model.

[v-janeyg-msft · Accepted solution]

Hi, @Avivek 

Try to combine this formula into your code:

Year =
IF (
    SELECTEDVALUE ( 'Table'[year] = "Current" ),
    YEAR ( 'Table'[Date] ) = YEAR ( TODAY () ),
    YEAR ( 'Table'[Date] )
)

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey