Forum Discussion

AishwariyaV's avatar
AishwariyaV
Helper IV
5 years ago
Solved

Dax correction

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?
Need Help
Show and Tell
  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    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)))

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      AishwariyaV
      Helper IV
      5 years ago

      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

    • AishwariyaV's avatar
      AishwariyaV
      Helper IV
      5 years ago

      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's avatar
        V-lianl-msft
        Community Support
        5 years ago

        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)))