Calculation based on Max Date in Date Slicer while ignoring Min Date

[kovacikm78]

Hello,
could you please help me with this measure, where I want calcutate sales for last 90 days while keeping
max date from the filter while ignoring min. date of the slicer.
The solution should also partly ignore the x axis with months. Let me explain: if I have a chart with months
eg. Nov2022, the result should be sum of all sales from Sep-01 to Nov30 (90 days).
I tryied all, removefilter... nothing helped.
This is the measure I would like to be reviewd:
CALCULATE([SalesSum_measure],Calendar[Date]>MAX(Calendar[Date])-90)
I am new with dax, so your help would be appreciated.

[ahmadibrahimbus · Accepted solution]

Dear @kovacikm78 ,

if I understand you correctly please use the below meausre:

Revenue PY =
CALCULATE(
SUM(Sales[Sales Amount]),
DATESINPERIOD(
'Date'[Date],
MAX('Date'[Date]),
-3,
MONTH
)
)

please refer to the DATESINPERIOD function in the below link:

DATESINPERIOD function (DAX) - DAX | Microsoft Learn

please markup this as an accepted solution if it works.

Best Regards,

[ahmadibrahimbus]

could you please share a sample of pbix file so we can help more.

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

=CALCULATE([SalesSum_measure],DATESBETWEEN(Calendar[Date],EDATE(MIN(Calendar[Date]),-2),MAX(Calendar[Date])))

To your x-axis, ensure that you drag Year and Month name from the Calendar Table.

Hope this helps.

[kovacikm78]

Hello,
thank you very much for you help. The calculeation works fine ony if I dont use timeline. Once I add months in a table or chart, the result is computed only for specific month and not for the whole period.
It should work even for days. So if I have in table specific day, it should return sum of  sales for previous two months. Now it returs only one day. Or one month, if I choose months view...
Is there a way it can be calculated that way?

[Ashish_Mathur]

You are welcome.  I am very confused about your requirement.  

[ahmadibrahimbus · Accepted solution]

Dear @kovacikm78 ,

if I understand you correctly please use the below meausre:

Revenue PY =
CALCULATE(
SUM(Sales[Sales Amount]),
DATESINPERIOD(
'Date'[Date],
MAX('Date'[Date]),
-3,
MONTH
)
)

please refer to the DATESINPERIOD function in the below link:

DATESINPERIOD function (DAX) - DAX | Microsoft Learn

please markup this as an accepted solution if it works.

Best Regards,

[kovacikm78]

Hello,
thank you very much for you help. The calculeation works fine ony if I dont use timeline. Once I add months in a table or chart, the result is computed only for specific month and not for the whole period (3 months back).
It should work even for days. So if I have in table specific day, it should return sum of  sales for previous three months. Now it returs only one day. Or one month, if I choose months view...
Is there a way it can be calculated that way?