Dax correction

[AishwariyaV]

Hi,

Measure = CALCULATE(SUM(SalesOrderDetails[Sales]),FILTER(SalesOrderDetails,
YEAR(SalesOrderDetails[DGIDate]) = YEAR(SalesOrderDetails[Current date]) &&
QUARTER(SalesOrderDetails[DGIDate]) = QUARTER(SalesOrderDetails[Current date]) &&
YEAR(SalesOrderDetails[FRGIDate]) = PREVIOUSYEAR(SalesOrderDetails[Current date]) &&
QUARTER(SalesOrderDetails[FRGIDate]) = PREVIOUSQUARTER(SalesOrderDetails[Current date])))

I am not sure what is wrong with this measure. It doesn't work when a category is selected.

My guess is it might be due to PREVIOUSYEAR and PREVIOUSQUARTER dax functions.
Can anyone help?

[amitchandak]

@AishwariyaV , whar are you trying to achieve here ?

refer these examples

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),PREVIOUSQUARTER(('Date'[Date])))

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[AishwariyaV]

FRGI date    DGI date   Sales
01/01/2021 01/04/2021 40
16/02/2021 01/04/2021 30
01/04/2021 01/07/2021 10
21/03/2021 12/07/2021 20
11/12/2020 01/04/2021 50

The result should be 40 + 30 = 70.

[V-lianl-msft · Accepted solution]

According to the data you provided, the result should be 80.

Measure = CALCULATE(
    SUM('Table'[Sales]),
    FILTER('Table',
        'Table'[FRGI date]<'Table'[DGI date]&&
        'Table'[FRGI date]>=DATEADD('Table'[DGI date].[Date],-1,QUARTER)))

[AishwariyaV]

Hi @V-lianl-msft ,

The FRGI date should be equal to previous quarter only and not the current quarter. 01/04/2021 (DD-MM-YYYY), falls under current quarter.

Let me give a try with the measure.

Thanks 😊

[AishwariyaV]

I want the sum of sales with filter as, 

FRGI Year = Year which falls in the previous quarter from current date

FRGI Quarter = Previous quarter from the current date

DGI year = current year from current date

DGI quarter = current quarter from current date